Search code examples
postgresqlpostgis

Use Query result as element of another query

I have this two queries,

SELECT ST_AsText(geom)
FROM areasTable
WHERE "Name" ILIKE 'Kachina';

Let's say that it returns a polygon value of: POLYGON((-XXX.XX XX.XXX, -XXX.XX XX.XXX, -XXX.XX XX.XXX, -XXX.XX XX.XXX)). I then use that value to do another search.

SELECT "ROAD_NAME"
FROM addresses 
WHERE ST_Contains(ST_GEOMFROMTEXT('POLYGON((-XXX.XX XX.XXX, -XXX.XX XX.XXX, -XXX.XX XX.XXX, -XXX.XX XX.XXX))',4326), addresses.geom);

What I have been trying to do is save a step and just find all the roads within a certain area without having to manually copy and paste the polygon of the area. Any ideas?

Solution

  • Try creating a stored procedure. https://www.w3schools.com/sql/sql_stored_procedures.asp

    Then write a program in a language that can interact with the SQL DB and run a loop that calls this stored procedure over and over.