Puzzled by SQL Code... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Puzzled by SQL Code...

Author

Topic

jigglitt
Starting Member

1 Post

Posted - 2008-10-22 : 11:21:04

I'm pretty new to SQL, and I'm attempting a spatial query for use in Postgres and Postgis. Basically, I have 2 spatial tables, both of polygons. What I want to be able to do is search for spatial coordinates using an identifier field (AIN) in one table, and then use those coordinates to determine what city they lie in in the other table. These are the queries I am currently using:

select asText(Centroid(the_geom)) from parcels where ain='4429037022';

This one gives me an output of:

"POINT(6418010.12090923 1851488.37665175)"

Then I enter this point into the second query:

select city_comm_ from city_boundaries where GeomFromText('Point(6418010.12090923 1851488.37665175)', 4269) && the_geom

This one gives me an output of:

LOS ANGELES

What I would like to do, is take the results of the first query and feed them to the second query, so that the only input by the user is the ain in the first one.

Hope this all makes sense. Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 11:26:38

this is MS SQL Server forum. post in some other forums if you're using db engine other than sql server.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 11:50:52

didnt understand on what basis you decide that value used inside the GeomfromText should be one returned by query for ain='4429037022'

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-10-22 : 13:39:12

Assuming that strange 4269 constant is some sort of format identifier for textual representations of a geometric type, it could be something like:


SELECT CB.city_comm_
FROM city_boundaries AS CB
INNER JOIN parcels AS P
  ON Centroid(P.the_geom) && CB.the_geom
WHERE P.ain = '4429037022'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources