10

I have a series of lat and long coordinates I've loaded into a spacial table (Nov01) in PostGIS, and I've created a geometry column in the table. I also have a shapefile (greatermanchester) I've loaded in with the POSTGIS 2.0 shapefile loader. I've double checked that they both have the same SRID of 4326, and then I ran the code

select a.*, b.*
from "Nov01" a
join greatermanchester b
on ST_Intersects(a.the_geom, b.geom)
limit 10

However it doesn't return any results. My points table looks like: enter image description here

What might I have gotten wrong?

EDIT: There definitely should be points within the polygon. I loaded the original csv and shapefile into QGIS and got this: enter image description here

I also ran explain analyze queries with these results:

EXPLAIN ANALYZE VERBOSE select count(*) 
from "Nov01" a 
join greatermanchester b 
on ST_Intersects(a.the_geom, b.geom)

result: enter image description here

6
  • 1
    Is the the_geom column the geometry representation of your lat/lon columns? If so, I would check to see if the lat/lon values got reversed when creating the_geom. Feb 10 '17 at 10:33
  • To elaborate on @JayCummins' suggestion: in popular parlance we say "lat-lon" but in correct GIS usage it should be "lon-lat" (i.e. x then y) Feb 10 '17 at 10:37
  • The code I used was: SET the_geom = ST_GeomFromText('POINT(' || lon || ' ' || lat || ')',4326); /i think this is the right way round Feb 10 '17 at 10:39
  • It looks right to me--and I've got a simulated version of your query that returns everytime. Can you do a: EXPLAIN ANALYZE VERBOSE select a.*, b.* from "Nov01" a join greatermanchester b on ST_Intersects(a.the_geom, b.geom) limit 10 and post the results?` Feb 10 '17 at 18:59
  • And maybe also do a EXPLAIN ANALYZE VERBOSE select count(*) from "Nov01" a join greatermanchester b on ST_Intersects(a.the_geom, b.geom) (without the LIMIT). Feb 10 '17 at 19:00
10

You should use the ST_within function and not the ST_intersect function. Here the documentation. Here the sql code. You want only the points within greatermanchester

select a.*
from "Nov01" as a
join greatermanchester as b
on ST_WITHIN(a.the_geom, b.geom)
5
  • and maybe selecting columns explicitly will help as well: gis.stackexchange.com/questions/219067/…
    – Mazu_R
    Jun 1 '18 at 13:18
  • 2
    why join and not a where clause?
    – Zach Smith
    Jun 5 '20 at 12:43
  • 1
    @ZachSmith the ON of the join clause serves the purpose of WHERE. WHERE is generally used to create a subset of something while JOIN is used to associate one thing with another, in this case associating points with a given polygon. Jun 10 '20 at 13:11
  • I've noticed that where also works though. Seems like it achieves the same thing (at least in my query). Is there anything to be aware of?
    – Zach Smith
    Jun 11 '20 at 4:48
  • 1
    As we are using an inner join they produce the same result and should produce as well the same query plan (stackoverflow.com/questions/354070/…)
    – Mazu_R
    Oct 5 '20 at 12:10

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.