PostGIS: Simple query selecting nodes of polygon

Question

Asked also at stackoverflow

INPUT:

1. Regular OSM buildings .shp table
2. PostGIS Point location
3. RADIUS (around above location), lets say 500m.

RESULT:

I want to get back all corners from all buildings within this radius from location, that ("WHERE") they distance from location is less than DISTANCE (let's say 200m).

I'm struggling to get result as points accepting WHERE clause from polygons table.

EDIT:

I started something like this:

SELECT ST_AsText((ST_DumpPoints(geom)).geom)
FROM buildings
WHERE ST_DWithin((ST_DumpPoints(geom).geom), ST_GeomFromText('POINT(5 45)',4326),  0.002);

Answer 1

You're using 4326 as your SRID in the point constructor. If that's also the SRID of your geometry column, you're asking the query for every point within 100 degrees of the query point (if it isn't, you should be getting a conflict error). You probably want to be using a different SRID, one whose units are in feet or metres.

If you can do something like:

SELECT *
FROM buildings
WHERE ST_DWithin(ST_Transfom(ST_GeomFromText('POINT(4 45)', 4326), 26910), 
    ST_Transform(geom, 26910), 100);

This transforms the query point from WGS84 to UTM10N, then thransforms the geometry colum to the same CRS and searches within 100m (obviously, your SRID will probably be different.) This assumes you intended your query to be in degrees latitude and longitude (which seems doubtful.)

As for the answer to the question:

You should probably use a subquery to create a set of individual points to query on.

WITH t AS (SELECT *, (ST_DumpPoints(geom)).geom AS pt) 
SELECT ST_AsText(pt) 
FROM t 
WHERE ST_DWithin(ST_GeomFromText('POINT(5 45)', 4326), pt, 4326);

Here, each row of the subquery has an individual point from the building, and also enough information to identify the building it came from. The distance query itself now operates on individual points, rather than sets of points. But it's still using degrees, which is not a great idea.