Oracle 18c: I have mapping software that has a limitation where it can only handle a single geometry column per table. If there are multiple geometry columns in a given table, then it will throw an error. So, I want to find a way to add an additional geometry column to a table, but store it as a datatype that
Tag: oracle-spatial
Why does (SHAPE).SDO_ORDINATES(1) syntax fail, but (SHAPE).ST_PointN(1) succeeds?
Oracle 18c: In a related question, we determined: The syntax of extracting collection elements by index is not supported in SQL. So a query that uses this syntax will fail: (shape).sdo_ordinates(1), Source: Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in SQL? However, I have a query that is similar…
Get XY coordinates from GEOM_SEGMENT_START_PT() output
Oracle 18c: How can I get the X & Y coordinates from GEOM_SEGMENT_START_PT()’s resulting LRS point? Note: The coordinates are stored in the SDO_ORDINATE_ARRAY( ), not the SDO_POINT. Desired output: Normally, I’d use something like a.shape.sdo_point.x. But I don’t think that applies here …
Determine what rows are causing error in query
I have an Oracle 18c table that has 15,000 rows. As a test, I’m trying to run the following query on it: When I run that query in SQL Developer, it initially runs without errors, but that’s just because it’s only selecting the first 50 rows. If I try to run the query on all rows (via CTRL+EN…
Convert SDO_GEOMETRY’s sdo_point attribute to text
I have SDO_GEOMETRY objects in Oracle 18c: In a query, I want to select the SDO_GEOMETRY’s sdo_point attribute as literal text (for concatenation purposes). Example: (fails) I don’t know how to convert that object attribute to text. I’ve tried using the SDO_UTIL.TO_WKTGEOMETRY() function. Bu…
Convert VARRAY to text (for concatenation)
I have a query that outputs a VARRAY: In SQL Developer, the VARRAY gets output as text: Question: Similar to what SQL Developer does, is there a way to convert the VARRAY to text using SQL — so that I can concatenate the value in a string? Example: (fails) Just a heads up: DB<>FIDDLE doesn’t seem …
Generate rows from JSON (SDO_GEOMETRY)
I have a multi-part SDO_GEOMETRY that I can represent as JSON (Oracle 21c): As an experiment, I want to find a way to generate rows from the JSON: Each row is a vertex of the geometry. Question: In an SQL query, is there a way to generate rows for each of the JSON vertices? I’m aware that SDO_GEOMETRY h…