I have a functional SQL query which works in Oracle Spatial, but does not work in PostGis. What works in Oracle spatial:

"CREATE TABLE mestske_komunikace AS
FROM lt_cze_5_0_points_sjtsk r, moravskoslezsky_kraj u

This returns me picture number 1.

I had to change SDO_Interesection to ST_Intersection in Postgis. But the script below does not works as it should. It shows me Metropolitan Road even in another region. Do you know why intersection does not work in the Postgis or how to rewrite for it to work? And another question is, it shows me roads in points and i would like to have the roads in lines, is it possible?

CREATE TABLE mestske_komunikace AS
FROM lt_cze_5_0_points_sjtsk r, moravskoslezsky_kraj u
(ST_Intersection(r.geom, u.geom) IS NOT NULL)

Picture number 2

Picture number 1

Picture number 2

  • You want to test on the Boolean function ST_Intersects – Vince Nov 9 '16 at 13:11
  • 2
    In answer to your question why "it doesn't work", it's because PostGIS' understanding of spatial operations is slightly more sophisticated than Oracle's. The intersection of two disjoint objects is not NULL, it's EMPTY, which has different semantics to NULL. That said, all the advice in the ticket is right: you want to use a boolean predicate, not compute an intersection, as that is much more CPU expensive than just getting a true/false answer. – Paul Ramsey Nov 9 '16 at 15:49

for your first question :

st_intersection returns a geometry. if you just want to know which roads intersect the region, you should use st_intersects, which returns a boolean (true/false)

like this : WHERE st_intersects(r.geom,u.geom) = 'TRUE'

but if your roads are longer than the boundaries of your region and you only want the part of the geometry that intersects the region, then you need st_intersection. ST_intersection must be placed in the SELECT and replace the geometry column. you can try like this :

SELECT r.field1, r.field2,..., st_intersection(r.geom,u.geom)

st_intersection takes more time than st_intersects. if the geometry of your original layer is point, i would suggest using st_intersects.

postgis doc for these functions :



second question : according to its name, your r layer seems to be points, that is why it returns points. If you want lines, try with a line layer (you seem to have one according to your screenshots) or try a join between your points selection and your line layer

  • Hello, thank you for posts. But it still does not work. I tried it like that codeWHERE (st_intersects(r.geom, u.geom)) AND (r.urban='1');code and even like you posted (st_intersects(r.geom, u.geom) = 'TRUE') AND (r.urban='1'); but it still shows me the roads in all regions. To your second answer, do you know how to use join betwen points and line layer in this case? Thank you very much again. – Marty Nov 13 '16 at 16:11
  • to answer your first question : i don't know if this is the problem but you don't need to put the function into brackets. the correct syntax should be to answer your second question : it depends of the structure of your database. if your data is structured to compute network analysis, you should have a key that connects the nodes and the edges. use this key for your join. – bliblablo35 Nov 14 '16 at 11:35
  • sorry i don't know how to edit my previous comment. the correct syntax should be : CREATE TABLE mestske_komunikace AS SELECT r.* FROM lt_cze_5_0_points_sjtsk r, moravskoslezsky_kraj u WHERE st_intersects(r.geom, u.geom) = 'TRUE' AND r.urban='1'; PS : please ask only one question at a time to make it easier to answer – bliblablo35 Nov 14 '16 at 11:39
  • Thank you a lot, now it works:) Now i just need display the roads in lines instead of points. Yes, you are right, these data are points (there are mentioned informations about urban and not urban roads), another data are in lines as you can see in pictures above. – Marty Nov 14 '16 at 12:59

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.