5

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

  • 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. – Jay Cummins 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) – MappaGnosis 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 – Joshua Kidd 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?` – Jay Cummins 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). – Jay Cummins Feb 10 '17 at 19:00
3

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)

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.