Use Query result as element of another query-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

I am including this as a help to someone who may be curious about how I solved this, but technically, MaximumBoy pointed me to the direct answer to my question, and for that reason I am going to vote for his answer as the correct one. I couldn't figure out how to do it Maximum's way, but that is because I know very little of SQL.

This is how I accomplished what I wanted. NOTICE THAT I changed the table name from "areasTable" to "areas" in my solution.

First way,


SELECT
    "ROAD_NAME"
FROM
    addresses
    JOIN areas ON ST_Contains(areas.geom, addresses.geom)
WHERE
    areas. "Name" ILIKE 'KACHINA';

The second way to accomplish this is the following,

SELECT
    addresses."ROAD_NAME"
FROM
    areas, addresses
WHERE
    areas."Name" ILIKE 'KACHINA'
    AND ST_Contains(areas.geom, addresses.geom);

The first one is a little bit slower than the second query, but this is from my empirical observations.