oracle sql query for fetching data from table having a polygon st_geometry datatype

AdeebFarhan — Wed, 25 Nov 2020 20:29:14 GMT

Dears

We have a featureclass containing city names and it’s border as polygon shape.The column is st_geometry. We are trying to fetch the city based on the coordinates using the below st geometry query. Even though the coordinates are exactly with in the border the query is not fetching any records.

select NAME1 from GTSOWNER.CM_BOUNDARY3_PG where sde.st_within (sde.st_point(45.576579,26.460792,32637),shape)=1;

Tried with multiple options without any luck

The database is oracle. the arcGIs version is 10.2

AdeebArcCatelog table descirptionarcatelog -coordinate systemTable data

Re: oracle sql query for fetching data from table having a polygon st_geometry datatype

VinceAngelo — Thu, 26 Nov 2020 03:33:58 GMT

Always make sure you define the correct spatial reference ID in table creation and population, then use that same spatial reference to define the geometry you construct in the query. The comparison operators all do an initial test of "Do the spatial references match?" and if that fails don't bother to attempt further comparison.

You've skirted this problem, then run into the other side of the equation: You've requested a geometry 52.7 meters from the origin of UTM Zone 37N, which is in the Rahole National Reserve in Kenya, instead of a place 242 km northwest of Riyadh, Saudi Arabia.

You need to use SDE.ST_Transform() to convert the lon/lat pair to EPSG 32637, something like:

SELECT name1 FROM gtsowner.cm_boundary3_pg WHERE sde.ST_Within( sde.ST_Transform( sde.ST_Point(45.576579,26.460792,4326), 32637), shape)=1;

except you need to use the spatial reference IDs (which are defined locally, so I can't know what yours might be), not the coordinate system IDs. You might need to create a layer with the 4326 coordinate system so that a spatial reference exists (if you haven't already).

- V

topic oracle sql query for fetching data from table having a polygon st_geometry datatype in Geodatabase Questions

oracle sql query for fetching data from table having a polygon st_geometry datatype

Re: oracle sql query for fetching data from table having a polygon st_geometry datatype