I have a query in Oracle to select polygons from my_polygon_table that interact or reside inside of a single polygon residing in border_table:

Select t1.*, t2.*, sdo_geom.sdo_intersection(t1.geoloc, t2.geom, 2 ) as genericgeo From      ( sdo_join ('my_border_table', 'geoloc', 'my_polygon_table', 'geom', 'mask=ANYINTERACT' ) ) j,
 my_border_table t1,my_polygon_table t2 
 (j.rowId1 = t1.rowId and j.rowId2 = t2.rowId) and (t1.geoloc.sdo_gtype = 2003 or t1.geoloc.sdo_gtype = 2007) and (t2.geom.sdo_gtype = 2003 or t2.geom.sdo_gtype = 2007) 

So far I've installed geometry with CREATE EXTENSION postgis; and copied tables. When I run a query in pgAdmin I get

ERROR:  syntax error at or near "table"
LINE 2: ...ntersection(t1.geoloc, t2.geom, 2 ) as genericgeo From table
SQL state: 42601
Character: 129

How to do the same thing with PostgreSQL?

  • @CL Query fails because ther is no sdo_join in PostGIS. table has no effect because it selects from sdo_join(/*..*/) j. I removed keyword table to avoid confusion. Oracle still works, PostGIS still fails. – Stepan Jul 3 at 12:08

PostgreSQL/PostGIS implement the SQL/MM specification, so just write it in proper SQL:

FROM my_border_table  t1
JOIN my_polygon_table t2 ON ST_Intersects(t1.geoloc, t2.geom)
WHERE ST_Dimension(t1.geoloc) = 2
  AND ST_Dimension(t2.geom  ) = 2;

-- or:
--     ST_GeometryType(t1.geoloc) IN ('ST_Polygon', 'ST_MultiPolygon')
-- AND ST_GeometryType(t2.geom  ) IN ('ST_Polygon', 'ST_MultiPolygon')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.