[postgis-users] 2 Simple Questions

[Markus Schaber]

Hi, Dylan,

Dylan Lorimer wrote:

> Many thanks. That pretty much worked for getting that 3rd coordinate.
> Any idea if it is possible to get the results as a comma delimited
> values rather than the EWKT?

Brent already posted a solution for this, you could also use a REGEX
pattern to the EWKT, or use

SELECT X(geom)||','||Y(geom)||','||Z(force_3d(geom)) , other, columns,
you, want FROM ....

> As for 'better', I thought I could pull this off using a join but
> couldn't figure it out. Is that possible?

I would not say that a subselect is clunky, or specifically worse than a
JOIN.

A join query could go along the lines of

SELECT a.city_name FROM gnis AS a JOIN gnis AS b ON
distance_sphere(a.the_geom, b.the_geom) < 18046.7 WHERE b.city_name='San
Francisco' AND b.country_code='US' and b.sub_national_code='CA';


But I doubt that they make any substantial difference in execution time.

Usig a geometrical index will give you some substantial speed
improvement if your table is large, and your query hits only a small
part of the table.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org