Skip to content

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 (different datatype) that succeeds when I use seemingly similar syntax:

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 since the coordinates aren’t stored in the SDO_POINT. Related: Oracle Spatial Community – Get X & Y

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+END), then it

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 to output VARRAYS correctly.

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 has a