I have these database tables defined:
CREATE TABLE regions ( id SERIAL PRIMARY KEY, name VARCHAR(150) NOT NULL ) CREATE TABLE region_points ( id SERIAL PRIMARY KEY, region_id SERIAL REFERENCES regions (id) ON DELETE CASCADE, index INTEGER NOT NULL, point GEOMETRY(Point, 4326) NOT NULL ) CREATE TABLE stations ( id SERIAL PRIMARY KEY, name VARCHAR(150) NOT NULL, point GEOMETRY(Point, 4326) NOT NULL )
region is a polygon, its points being defined in the
region_points table. A
station may reside at a single point somewhere inside a
I'd like to write a single query that gets all
stations inside a particular
region polygon. At the moment, I don't know how, so I do it in multiple steps:
# 1. Get all points that comprise the region polygon. SELECT point FROM region_points WHERE region_id = 1234 ORDER BY index # 2. Programmatically (non-SQL) construct a linestring from the points. Not shown here. # 3. Get all stations inside the polygon. SELECT id, name, point FROM ( SELECT ST_GeomFromText($1, 4326) AS polygon ) AS query1, ( SELECT id, name, point FROM stations ) AS query2 WHERE ST_Contains(query1.polygon, query2.point)
As you can see, I have to construct the linestring programmatically and then pass it into the query as an argument to
ST_GeomFromText, because I don't know how to do it in SQL.
I don't want to do anything programmatically. How can I re-write all of this as one SQL query?