0

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 d
WHERE
  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. The data origins from OpenStreetMap. I hope the collection of way objects I exported from OSM is interpreted as a polygon by PostGIS. 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"

The database table district looks like this:

CREATE TABLE public.district
(
  ogc_fid serial NOT NULL,
  the_geom geometry(LineString,4326),
  name character varying(254),
  cmt character varying(254),
  "desc" character varying(254),
  src character varying(254),
  link1_href character varying(254),
  link1_text character varying(254),
  link1_type character varying(254),
  link2_href character varying(254),
  link2_text character varying(254),
  link2_type character varying(254),
  "number" numeric(10,0),
  type character varying(254),
  CONSTRAINT districts_pkey PRIMARY KEY (ogc_fid)
)

Do I need to SELECT the district table as a POLYGON first?

Improve this question

1 Answer 1

Reset to default
1

Did you read http://postgis.net/docs/ST_Contains.html?

ST_Contains — Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.

Creating SQL queries which construct geometry from WKT is a good and controlled way for testing the syntax

select ST_Contains(
ST_GeomFromText('POINT (418 411)'),
ST_GeomFromText('POLYGON (( 322 322, 322 513, 528 513, 528 322, 322 322 ))'))

f

select ST_Contains(
ST_GeomFromText('POLYGON (( 322 322, 322 513, 528 513, 528 322, 322 322 ))'),
ST_GeomFromText('POINT (418 411)'))

t

Improve this answer
6
  • So another function would make more sense?
    – JJD
    Jul 18, 2015 at 12:58
  • Using geometries in correct order would make sense. Your query is testing if there is a polygon inside a point. Put polygon first into your query and you will find locations which are inside the polygon. You may want to select just one district from you table and then you must add something like AND district.id=xxx.
    – user30184
    Jul 18, 2015 at 14:12
  • The table district contains only one polygon. I swapped the polygon with the locations: ... ST_CONTAINS(d.the_geom, l.the_geom); The result is still empty though. With reference to the documentation you mentioned: does ST_CONTAINS return true for my query since there are locations outside the polygon, too?!
    – JJD
    Jul 19, 2015 at 10:42
  • Oh, your district is not a polygon but a linestring. It does not contain anything that is not on the borderline. You must use table where district is as a polygon.
    – user30184
    Jul 19, 2015 at 12:42
  • How do I handle the LineString data correctly as a polygon? Can I convert it while importing or with PostGIS functions? I guess ST_MakePolygon(d.the_geom) should do it. It returns ERROR: lwpoly_from_lwlines: shell must be closed which indicates that the district is not closed.
    – JJD
    Jul 19, 2015 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.