Question 114322 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search