[postgis-users] 2 Simple Questions

[Dylan Lorimer]

Hi Markus,

That join was exactly what I was looking for; I appreciate it. You're
probably correct that it won't be any more efficient, but not being a
big db guy I was banging my head on the table trying to figure out the
proper syntax.

As for indices, I created a GIST on my geometry and BTREE's on my most
used attributes in WHERE clauses. Does that sound about right?

Much appreciated.
-dylan

On 10/6/06, Markus Schaber <schabi at logix-tt.com> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


-- 
E. Dylan Lorimer | Google Earth Enterprise
650.253.2459 (O) | 650.862.6810 (M) | 650.644.0182 (F)