13

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

Joshua Kidd
  • 565
  • 1
  • 4
  • 15
  • 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
  • I've updated my question with the results. I'm certain it's some sort of problem with my shapefile. I drew a simple polygon in QGIS to test and successfully ran the query against that instead of greatermanchester. – Joshua Kidd Feb 13 '17 at 16:06

1 Answers1

14

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)
Mazu_R
  • 1,102
  • 9
  • 15
  • and maybe selecting columns explicitly will help as well: https://gis.stackexchange.com/questions/219067/qgis-db-manager-load-new-layer-not-loading-sql-query – Mazu_R Jun 01 '18 at 13:18
  • 2
    why join and not a where clause? – Zach Smith Jun 05 '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. – Hugh_Kelley 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 04:48
  • 1
    As we are using an inner join they produce the same result and should produce as well the same query plan (https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) – Mazu_R Oct 05 '20 at 12:10