I want to create a database view in Postgres/PostGIS which returns a subset of the points stored in another table called locations. The subset is defined by an area stored in yet another table called district.
I came up with the following query inspired by this answer:
SELECT l.*FROM public."locations" AS l, public."district" AS dWHERE ST_CONTAINS(l.the_geom, d.the_geom));This query returns an empty result.
I used QGIS to visually check that the district actually contains locations.
It might be that I store the district incorrectly. This is the command I used to import the Shapefiles into PostGIS:
$ ogr2ogr -update -append -f "PostgreSQL" \ PG:"host=localhost user=username port=5432 \ dbname=databasename password=password" \ district.shp -lco GEOMETRY_NAME=the_geom -nln "public.district"Do I need to SELECT the district table as a POLYGON first?
أكثر...
I came up with the following query inspired by this answer:
SELECT l.*FROM public."locations" AS l, public."district" AS dWHERE ST_CONTAINS(l.the_geom, d.the_geom));This query returns an empty result.
I used QGIS to visually check that the district actually contains locations.
It might be that I store the district incorrectly. This is the command I used to import the Shapefiles into PostGIS:
$ ogr2ogr -update -append -f "PostgreSQL" \ PG:"host=localhost user=username port=5432 \ dbname=databasename password=password" \ district.shp -lco GEOMETRY_NAME=the_geom -nln "public.district"Do I need to SELECT the district table as a POLYGON first?
أكثر...