PostGIS – How to Find Points Within a Polygon in PostGIS

point-in-polygonpostgis-2.0

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:

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:

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:

Best Answer

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)

Related Solutions

[GIS] nearest neighbor within table using PostGIS

There are various methods for determining nearest neighbor - I provide several examples in this blogpost (pre-2.0, however).

Here is an example using a postgres window function to rank the distances between points. Grab the highest - and thus closest - rank and recalculate the distance. If you have a general sense of how clustered your points are, you can throw a ST_DWithin() in your query to limit the initial distance calculations.

  SELECT
     i.gid
    ,i.b_gid
    ,ST_Distance(i.geom, i.b_geom) AS dist
FROM(
SELECT
     a.gid
    ,b.gid AS b_gid
    ,a.geom
    ,b.geom AS b_geom
    ,rank() OVER (PARTITION BY a.gid ORDER BY ST_Distance(a.centroid_geom, b.the_geom)) AS pos
FROM points a, points b 
WHERE a.gid <> b.gid) i
WHERE pos = 1

PostGIS – How to Populate the Geometry Column in a PostGIS Table?

Per comments, in occasions the geom column might not be visible to the client as a human readable string (a geom column is encoded as an EWTB - Extended Well Known Binary ).

There are two ways to visually check if the geom column is populated:

Load the table to your favourite gis client (eg. Qgis)
Count the NULL and NOT NULL values of the geom column :

      SELECT COUNT(geom), 'null_tally' AS narrative 
      FROM 'myTable' 
      WHERE a IS NULL 
      UNION
      SELECT COUNT(geom), 'not_null_tally' AS narrative 
      FROM 'myTable'  
      WHERE a IS NOT NULL;

count sql snippet from : https://stackoverflow.com/questions/1271810/counting-null-and-non-null-values-in-a-single-query )

Best Answer

Related Solutions

[GIS] nearest neighbor within table using PostGIS

PostGIS – How to Populate the Geometry Column in a PostGIS Table?

Related Question