[postgis-users] Re: postgis-users Digest, Vol 54, Issue 2
narayanan rm
narayanan.rm at gmail.com
Mon Apr 2 20:57:31 PDT 2007
Dear friends i could finally install the postgis .
Thanks to all of you
RM Narayanan
On 4/3/07, postgis-users-request at postgis.refractions.net
<postgis-users-request at postgis.refractions.net> wrote:
> Send postgis-users mailing list submissions to
> postgis-users at postgis.refractions.net
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> or, via email, send a message with subject or body 'help' to
> postgis-users-request at postgis.refractions.net
>
> You can reach the person managing the list at
> postgis-users-owner at postgis.refractions.net
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-users digest..."
>
>
> Today's Topics:
>
> 1. RE: Indexes not being used (Obe, Regina)
> 2. RE: Indexes not being used (Pedro Doria Meunier)
> 3. 10 closest units (Pedro Doria Meunier)
> 4. RE: 10 closest units (Obe, Regina)
> 5. SRID for analyzing a USA national data set in Meters (mfrumin)
> 6. RE: SRID for analyzing a USA national data set in Meters
> (Pedro Doria Meunier)
> 7. RE: 10 closest units (Pedro Doria Meunier)
> 8. RE: 10 closest units (Obe, Regina)
> 9. Re: SRID for analyzing a USA national data set in Meters
> (Michael Frumin)
> 10. Re: SRID for analyzing a USA national data set in Meters
> (Stephen Woodbridge)
> 11. RE: SRID for analyzing a USA national data set in Meters
> (Pedro Doria Meunier)
> 12. Re: SRID for analyzing a USA national data set in Meters
> (Paul Ramsey)
> 13. Re: SRID for analyzing a USA national data set in Meters
> (Michael Frumin)
> 14. Re: Indexes not being used (Paul Ramsey)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Sun, 1 Apr 2007 17:33:43 -0400
> From: "Obe, Regina" <robe.dnd at cityofboston.gov>
> Subject: RE: [postgis-users] Indexes not being used
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <53F9CF533E1AA14EA1F8C5C08ABC08D201979EA0 at ZDND.DND.boston.cob>
> Content-Type: text/plain; charset="iso-8859-1"
>
> I assume the srid of your t.geometry field is 4326.
>
> How do you have your index specified? If you have it something like
>
> CREATE INDEX idx_pt_madeira_topnymy_geometry ON pt_madeira_toponymy USING GIST(transform(geometry, 4326));
>
> rather than
> CREATE INDEX idx_pt_madeira_topnymy_geometry ON pt_madeira_toponymy USING GIST(geometry);
>
> I don't think it will recognize it as a valid index for your query since your query is using geometry rather than transform(geometry, 4326)
>
> Other thing to check - you should have an index on your parish field. I think that may have better selectivity depending on how many parish are 'SE'
>
> Hope that helps,
> Regina
>
>
> ________________________________
>
> From: postgis-users-bounces at postgis.refractions.net on behalf of Pedro Doria Meunier
> Sent: Sat 3/31/2007 2:57 PM
> To: 'PostGIS Users Discussion'
> Subject: [postgis-users] Indexes not being used
>
>
>
> Hi all (with a special wink to Regina ;- )
>
>
>
> This is the query not using indices:
>
>
>
> SELECT parish,county,geometry FROM pt_madeira_toponymy as t WHERE parish=upper('se') AND
>
> t.geometry && geomfromtext('POINT(-16.9213592631455 32.6437878212273)',4326) AND intersects(geomfromtext('POINT(-16.9213592631455 32.6437878212273)',4326), t.geometry);
>
>
>
> As you can plainly see it checks if a point is inside some polygon.
>
>
>
> EXPLAIN ANALYZE returns this:
>
> "Seq Scan on pt_madeira_toponymy t (cost=0.00..3.03 rows=1 width=96) (actual time=1.086..1.278 rows=1 loops=1)"
>
> " Filter: ((parish = 'SE'::text) AND (geometry && '0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry) AND intersects('0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry, geometry))"
>
> "Total runtime: 1.312 ms"
>
>
>
> The toponymy has two indices: one for 4326 and another for 32628 using GiST.
>
>
>
> Is the intersects function not using indices at all??
>
>
>
> Already thankful for any ideas,
>
> With best regards,
>
> Pedro Doria Meunier.
>
>
>
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20070401/86f7cbb8/attachment-0001.html
>
> ------------------------------
>
> Message: 2
> Date: Sun, 1 Apr 2007 23:10:01 +0100
> From: "Pedro Doria Meunier" <pdoria at netmadeira.com>
> Subject: RE: [postgis-users] Indexes not being used
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <000601c774aa$80639260$812ab720$@com>
> Content-Type: text/plain; charset="us-ascii"
>
> Hello Regina,
>
>
>
> Txs for replying.
>
>
>
> You're right. I was missing an index for the text fields. I realized this
> and immediately applied the theory with grim results. L
>
>
>
> It turns out (?) that one as to use TSearch2 to have the indices fired up
> for text fields. Someone please rebate me if I'm wrong.
>
>
>
> Anyway I'm still not ready to mess up with my tables' schema in case someone
> comes up with a more enlightened solution.
>
>
>
> Best regards,
>
> Pedro.
>
>
>
>
>
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
> Regina
> Sent: domingo, 1 de Abril de 2007 22:34
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] Indexes not being used
>
>
>
> I assume the srid of your t.geometry field is 4326.
>
>
>
> How do you have your index specified? If you have it something like
>
>
>
> CREATE INDEX idx_pt_madeira_topnymy_geometry ON pt_madeira_toponymy USING
> GIST(transform(geometry, 4326));
>
>
>
> rather than
>
> CREATE INDEX idx_pt_madeira_topnymy_geometry ON pt_madeira_toponymy USING
> GIST(geometry);
>
>
>
> I don't think it will recognize it as a valid index for your query since
> your query is using geometry rather than transform(geometry, 4326)
>
>
>
> Other thing to check - you should have an index on your parish field. I
> think that may have better selectivity depending on how many parish are 'SE'
>
>
>
> Hope that helps,
>
> Regina
>
>
>
>
>
> _____
>
> From: postgis-users-bounces at postgis.refractions.net on behalf of Pedro Doria
> Meunier
> Sent: Sat 3/31/2007 2:57 PM
> To: 'PostGIS Users Discussion'
> Subject: [postgis-users] Indexes not being used
>
> Hi all (with a special wink to Regina ;- )
>
>
>
> This is the query not using indices:
>
>
>
> SELECT parish,county,geometry FROM pt_madeira_toponymy as t WHERE
> parish=upper('se') AND
>
> t.geometry && geomfromtext('POINT(-16.9213592631455
> 32.6437878212273)',4326) AND
> intersects(geomfromtext('POINT(-16.9213592631455 32.6437878212273)',4326),
> t.geometry);
>
>
>
> As you can plainly see it checks if a point is inside some polygon.
>
>
>
> EXPLAIN ANALYZE returns this:
>
> "Seq Scan on pt_madeira_toponymy t (cost=0.00..3.03 rows=1 width=96)
> (actual time=1.086..1.278 rows=1 loops=1)"
>
> " Filter: ((parish = 'SE'::text) AND (geometry &&
> '0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry) AND
> intersects('0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry,
> geometry))"
>
> "Total runtime: 1.312 ms"
>
>
>
> The toponymy has two indices: one for 4326 and another for 32628 using GiST.
>
>
>
> Is the intersects function not using indices at all??
>
>
>
> Already thankful for any ideas,
>
> With best regards,
>
> Pedro Doria Meunier.
>
> _____
>
>
>
>
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended solely for the
> addressee. If you received this in error, please contact the sender
> and delete the material from any computer.
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20070401/0cd18b64/attachment-0001.html
>
> ------------------------------
>
> Message: 3
> Date: Mon, 2 Apr 2007 02:50:23 +0100
> From: "Pedro Doria Meunier" <pdoria at netmadeira.com>
> Subject: [postgis-users] 10 closest units
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <001101c774c9$481d0b60$d8572220$@com>
> Content-Type: text/plain; charset="us-ascii"
>
> Hi All,
>
>
>
> @Regina:
>
> Remember you helped me with the 10 closest units to a given point? This was
> it:
>
> (original layers srid==4326 - I want *meters* so the transform at play)
>
> SELECT u.id, u.friendly_name, u.curr_location, t.oid,
> distance(transform(u.curr_location,32628), transform(t.geometry, 32628)) AS
> thedistance, u.mobile FROM units AS u, (SELECT roads.oid, roads.geometry
> FROM roads WHERE name='N17' LIMIT 1) AS t
>
> ORDER BY thedistance LIMIT 10;
>
>
>
> (this query returns the 1st found line segment labelled 'N17' which is 68Kms
> away when the unit is actually 1.8 meters away from the closest line segment
> of the same label)
>
>
>
> @All
>
> This actually returns the FIRST occurrence of 'N17' (the sample).
>
> This is *NOT* necessarily the closest road to the unit's current location.
>
>
>
> I've melted half-a-dozen neurons (mainly due to tiredness at the time of
> this writing :] )trying to figure out how can I implement the distance bit
> in the sub-query.
>
>
>
> Given example for ONE unit:
>
> select u.friendly_name, distance(transform(u.curr_location, 32628),
>
> transform(geometry,32628)) as thedistance
>
> from pt_mainland_roads as r, units as u
>
> where name='N17' AND u.curr_location && r.geometry order by thedistance
> LIMIT 1;
>
>
>
> (this returns the correct result)
>
>
>
> So to summarize things:
>
> I need help with a query that returns the *FIRST TEN* units to the *closest
> given* road.
>
> (Keep in mind that multiple line segments with the same name exist - I need
> the closest)
>
>
>
> Will try again tomorrow with a clear head but in the meantime:
>
> Any help would be most appreciated!
>
>
>
> Best regards,
>
> Pedro Doria Meunier.
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20070402/ea144421/attachment-0001.html
>
> ------------------------------
>
> Message: 4
> Date: Mon, 2 Apr 2007 08:43:06 -0400
> From: "Obe, Regina" <robe.dnd at cityofboston.gov>
> Subject: RE: [postgis-users] 10 closest units
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <53F9CF533E1AA14EA1F8C5C08ABC08D201B98C4D at ZDND.DND.boston.cob>
> Content-Type: text/plain; charset="us-ascii"
>
> I'm not sure how slow this would be depending on how many line segments
> you have by name, but it seems the most speedy to write. Basically I
> think you want to collect all your roads of N17 into a single geometry
> so that you can then apply a single distance check call.
>
> So something like
>
> select u.friendly_name, distance(transform(u.curr_location, 32628),
>
> transform(rc.agg_geometry,32628)) as thedistance
>
> from (SELECT collect(r.geometry) as agg_geometry FROM pt_mainland_roads
> r WHERE r.name = 'N17') rc, units as u
>
> where rc.agg_geometry && u.curr_location order by thedistance LIMIT 10;
>
>
>
> If you have some units that are not in the bounding box of a road that
> would be in the top 10, then you may want to change your where clause to
>
>
>
> expand(rc.agg_geometry, <somevalue>) && u.curr_location
>
>
>
> Hope that helps,
>
> Regina
>
>
> ________________________________
>
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Pedro Doria Meunier
> Sent: Sunday, April 01, 2007 9:50 PM
> To: 'PostGIS Users Discussion'
> Subject: [postgis-users] 10 closest units
> Importance: High
>
>
>
> Hi All,
>
>
>
> @Regina:
>
> Remember you helped me with the 10 closest units to a given point? This
> was it:
>
> (original layers srid==4326 - I want *meters* so the transform at play)
>
> SELECT u.id, u.friendly_name, u.curr_location, t.oid,
> distance(transform(u.curr_location,32628), transform(t.geometry, 32628))
> AS thedistance, u.mobile FROM units AS u, (SELECT roads.oid,
> roads.geometry FROM roads WHERE name='N17' LIMIT 1) AS t
>
> ORDER BY thedistance LIMIT 10;
>
>
>
> (this query returns the 1st found line segment labelled 'N17' which is
> 68Kms away when the unit is actually 1.8 meters away from the closest
> line segment of the same label)
>
>
>
> @All
>
> This actually returns the FIRST occurrence of 'N17' (the sample).
>
> This is *NOT* necessarily the closest road to the unit's current
> location.
>
>
>
> I've melted half-a-dozen neurons (mainly due to tiredness at the time of
> this writing :] )trying to figure out how can I implement the distance
> bit in the sub-query...
>
>
>
> Given example for ONE unit:
>
> select u.friendly_name, distance(transform(u.curr_location, 32628),
>
> transform(geometry,32628)) as thedistance
>
> from pt_mainland_roads as r, units as u
>
> where name='N17' AND u.curr_location && r.geometry order by thedistance
> LIMIT 1;
>
>
>
> (this returns the correct result)
>
>
>
> So to summarize things:
>
> I need help with a query that returns the *FIRST TEN* units to the
> *closest given* road.
>
> (Keep in mind that multiple line segments with the same name exist - I
> need the closest)
>
>
>
> Will try again tomorrow with a clear head but in the meantime:
>
> Any help would be most appreciated!
>
>
>
> Best regards,
>
> Pedro Doria Meunier.
>
>
>
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20070402/748c28fd/attachment-0001.html
>
> ------------------------------
>
> Message: 5
> Date: Mon, 2 Apr 2007 06:11:55 -0700 (PDT)
> From: mfrumin <mfrumin at rpa.org>
> Subject: [postgis-users] SRID for analyzing a USA national data set in
> Meters
> To: postgis-users at postgis.refractions.net
> Message-ID: <9790471.post at talk.nabble.com>
> Content-Type: text/plain; charset=us-ascii
>
>
> I have a data set that includes points all over the United States in lat/lng
> (say, SRID = 4326) but I need to do some analysis on these data in real
> units, say meters. So, my task is to select an appropriate SRID that will
> work reasonably well for the whole USA to reproject lat/lng into meters.
> There are a bazillion SRID's available. Any suggestions?
>
> This analysis doesn't have to be super-precise, so I'm not so concerned with
> the limitations of a national projection (as opposed to state/region).
>
> thanks,
> Michael
>
> --
> View this message in context: http://www.nabble.com/SRID-for-analyzing-a-USA-national-data-set-in-Meters-tf3505664.html#a9790471
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
>
>
> ------------------------------
>
> Message: 6
> Date: Mon, 2 Apr 2007 14:29:42 +0100
> From: "Pedro Doria Meunier" <pdoria at netmadeira.com>
> Subject: RE: [postgis-users] SRID for analyzing a USA national data
> set in Meters
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <001601c7752a$fb03b970$f10b2c50$@com>
> Content-Type: text/plain; charset="us-ascii"
>
> Hello Michael,
>
> You determine the correct SRID with this bit of code (PHP):
> (the $loc was obtained from a SELECT with the astext(geometry) function)
>
> The idea behind this is summarized in the UtmZoneFromLong() function.
> Pick a Long value and calculate the utm zone for it.
> Then use the SridFromUtmZone() function to get the srid from the given mask
> (see below) --> this queries spatial_ref_sys with the calculated $utm param.
> SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS 84 / UTM
> zone $utm%' LIMIT 1
>
> // extract the POINT geometry
> $geometry = str_replace("POINT(","", $loc);
> $geometry = str_replace(")","",$geometry);
> $coordinate = explode(" ",$geometry);
> $lon=$coordinate[0];
> $lat=$coordinate[1];
>
> // get utm zone from coordinates
> $utm=UtmZoneFromLong($lon);
> // get srid from the spatial_ref_sys table for the given utm zone
> // add N or S
> if($lat>=0) $utm.="N";
> else $utm.="S";
> $srid=SridFromUtmZone($utm, $connection);
>
>
> // determine UTM zone from LonLat coordinates
> // params $long: longitude
> // returns (int) utm
> function UtmZoneFromLong($lon) {
> $utm = floor(($lon + 180) / 6) + 1;
> return $utm;
> }
>
> // query the spatial_ref_sys table for the srid of the given utm zone
> // params $utm: utm zone string (MUST BE TRAILED WITH 'N' OR 'S'!), $conn:
> connection to the db
> // returns (int) srid
> function SridFromUtmZone($utm, $conn){
> $myresult = pg_exec($conn, "SELECT srid FROM spatial_ref_sys WHERE srtext
> LIKE 'PROJCS[\"WGS 84 / UTM zone $utm%' LIMIT 1");
> $srid=pg_result($myresult, 0, 0);
> return $srid;
> }
>
> HTH,
> Pedro Doria Meunier
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of mfrumin
> Sent: segunda-feira, 2 de Abril de 2007 14:12
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] SRID for analyzing a USA national data set in
> Meters
>
>
> I have a data set that includes points all over the United States in lat/lng
> (say, SRID = 4326) but I need to do some analysis on these data in real
> units, say meters. So, my task is to select an appropriate SRID that will
> work reasonably well for the whole USA to reproject lat/lng into meters.
> There are a bazillion SRID's available. Any suggestions?
>
> This analysis doesn't have to be super-precise, so I'm not so concerned with
> the limitations of a national projection (as opposed to state/region).
>
> thanks,
> Michael
>
> --
> View this message in context:
> http://www.nabble.com/SRID-for-analyzing-a-USA-national-data-set-in-Meters-t
> f3505664.html#a9790471
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> ------------------------------
>
> Message: 7
> Date: Mon, 2 Apr 2007 14:32:00 +0100
> From: "Pedro Doria Meunier" <pdoria at netmadeira.com>
> Subject: RE: [postgis-users] 10 closest units
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <001701c7752b$4bb90a50$e32b1ef0$@com>
> Content-Type: text/plain; charset="us-ascii"
>
> Regina,
>
>
>
> You're simply an angel! Thank you!
>
>
>
> Just one question:
>
> The expand(rc.agg_geometry, 0.5) uses *degrees*, right?
>
>
>
> All the best,
>
> Pedro.
>
>
>
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
> Regina
> Sent: segunda-feira, 2 de Abril de 2007 13:43
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] 10 closest units
>
>
>
> I'm not sure how slow this would be depending on how many line segments you
> have by name, but it seems the most speedy to write. Basically I think you
> want to collect all your roads of N17 into a single geometry so that you can
> then apply a single distance check call.
>
>
>
> So something like
>
>
>
> select u.friendly_name, distance(transform(u.curr_location, 32628),
>
> transform(rc.agg_geometry,32628)) as thedistance
>
> from (SELECT collect(r.geometry) as agg_geometry FROM pt_mainland_roads r
> WHERE r.name = 'N17') rc, units as u
>
> where rc.agg_geometry && u.curr_location order by thedistance LIMIT 10;
>
>
>
> If you have some units that are not in the bounding box of a road that would
> be in the top 10, then you may want to change your where clause to
>
>
>
> expand(rc.agg_geometry, <somevalue>)&& u.curr_location
>
>
>
> Hope that helps,
>
> Regina
>
>
>
> _____
>
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Pedro
> Doria Meunier
> Sent: Sunday, April 01, 2007 9:50 PM
> To: 'PostGIS Users Discussion'
> Subject: [postgis-users] 10 closest units
> Importance: High
>
> Hi All,
>
>
>
> @Regina:
>
> Remember you helped me with the 10 closest units to a given point? This was
> it:
>
> (original layers srid==4326 - I want *meters* so the transform at play)
>
> SELECT u.id, u.friendly_name, u.curr_location, t.oid,
> distance(transform(u.curr_location,32628), transform(t.geometry, 32628)) AS
> thedistance, u.mobile FROM units AS u, (SELECT roads.oid, roads.geometry
> FROM roads WHERE name='N17' LIMIT 1) AS t
>
> ORDER BY thedistance LIMIT 10;
>
>
>
> (this query returns the 1st found line segment labelled 'N17' which is 68Kms
> away when the unit is actually 1.8 meters away from the closest line segment
> of the same label)
>
>
>
> @All
>
> This actually returns the FIRST occurrence of 'N17' (the sample).
>
> This is *NOT* necessarily the closest road to the unit's current location.
>
>
>
> I've melted half-a-dozen neurons (mainly due to tiredness at the time of
> this writing :] )trying to figure out how can I implement the distance bit
> in the sub-query.
>
>
>
> Given example for ONE unit:
>
> select u.friendly_name, distance(transform(u.curr_location, 32628),
>
> transform(geometry,32628)) as thedistance
>
> from pt_mainland_roads as r, units as u
>
> where name='N17' AND u.curr_location && r.geometry order by thedistance
> LIMIT 1;
>
>
>
> (this returns the correct result)
>
>
>
> So to summarize things:
>
> I need help with a query that returns the *FIRST TEN* units to the *closest
> given* road.
>
> (Keep in mind that multiple line segments with the same name exist - I need
> the closest)
>
>
>
> Will try again tomorrow with a clear head but in the meantime:
>
> Any help would be most appreciated!
>
>
>
> Best regards,
>
> Pedro Doria Meunier.
>
> _____
>
>
>
>
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended solely for the
> addressee. If you received this in error, please contact the sender
> and delete the material from any computer.
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20070402/eb6a9928/attachment-0001.html
>
> ------------------------------
>
> Message: 8
> Date: Mon, 2 Apr 2007 09:37:11 -0400
> From: "Obe, Regina" <robe.dnd at cityofboston.gov>
> Subject: RE: [postgis-users] 10 closest units
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <53F9CF533E1AA14EA1F8C5C08ABC08D201B98D02 at ZDND.DND.boston.cob>
> Content-Type: text/plain; charset="us-ascii"
>
> Its whatever the metric of your r.geometry field so if that is in
> degrees then yes it would be in degrees.
>
> Thanks,
> Regina
>
> ________________________________
>
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Pedro Doria Meunier
> Sent: Monday, April 02, 2007 9:32 AM
> To: 'PostGIS Users Discussion'
> Subject: RE: [postgis-users] 10 closest units
> Importance: High
>
>
>
> Regina,
>
>
>
> You're simply an angel! Thank you!
>
>
>
> Just one question:
>
> The expand(rc.agg_geometry, 0.5) uses *degrees*, right?
>
>
>
> All the best,
>
> Pedro.
>
>
>
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
> Regina
> Sent: segunda-feira, 2 de Abril de 2007 13:43
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] 10 closest units
>
>
>
> I'm not sure how slow this would be depending on how many line segments
> you have by name, but it seems the most speedy to write. Basically I
> think you want to collect all your roads of N17 into a single geometry
> so that you can then apply a single distance check call.
>
>
>
> So something like
>
>
>
> select u.friendly_name, distance(transform(u.curr_location, 32628),
>
> transform(rc.agg_geometry,32628)) as thedistance
>
> from (SELECT collect(r.geometry) as agg_geometry FROM pt_mainland_roads
> r WHERE r.name = 'N17') rc, units as u
>
> where rc.agg_geometry && u.curr_location order by thedistance LIMIT 10;
>
>
>
> If you have some units that are not in the bounding box of a road that
> would be in the top 10, then you may want to change your where clause to
>
>
>
> expand(rc.agg_geometry, <somevalue>)&& u.curr_location
>
>
>
> Hope that helps,
>
> Regina
>
>
>
> ________________________________
>
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Pedro Doria Meunier
> Sent: Sunday, April 01, 2007 9:50 PM
> To: 'PostGIS Users Discussion'
> Subject: [postgis-users] 10 closest units
> Importance: High
>
> Hi All,
>
>
>
> @Regina:
>
> Remember you helped me with the 10 closest units to a given point? This
> was it:
>
> (original layers srid==4326 - I want *meters* so the transform at play)
>
> SELECT u.id, u.friendly_name, u.curr_location, t.oid,
> distance(transform(u.curr_location,32628), transform(t.geometry, 32628))
> AS thedistance, u.mobile FROM units AS u, (SELECT roads.oid,
> roads.geometry FROM roads WHERE name='N17' LIMIT 1) AS t
>
> ORDER BY thedistance LIMIT 10;
>
>
>
> (this query returns the 1st found line segment labelled 'N17' which is
> 68Kms away when the unit is actually 1.8 meters away from the closest
> line segment of the same label)
>
>
>
> @All
>
> This actually returns the FIRST occurrence of 'N17' (the sample).
>
> This is *NOT* necessarily the closest road to the unit's current
> location.
>
>
>
> I've melted half-a-dozen neurons (mainly due to tiredness at the time of
> this writing :] )trying to figure out how can I implement the distance
> bit in the sub-query...
>
>
>
> Given example for ONE unit:
>
> select u.friendly_name, distance(transform(u.curr_location, 32628),
>
> transform(geometry,32628)) as thedistance
>
> from pt_mainland_roads as r, units as u
>
> where name='N17' AND u.curr_location && r.geometry order by thedistance
> LIMIT 1;
>
>
>
> (this returns the correct result)
>
>
>
> So to summarize things:
>
> I need help with a query that returns the *FIRST TEN* units to the
> *closest given* road.
>
> (Keep in mind that multiple line segments with the same name exist - I
> need the closest)
>
>
>
> Will try again tomorrow with a clear head but in the meantime:
>
> Any help would be most appreciated!
>
>
>
> Best regards,
>
> Pedro Doria Meunier.
>
> ________________________________
>
>
>
>
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended solely for the
> addressee. If you received this in error, please contact the sender
> and delete the material from any computer.
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20070402/5943687b/attachment-0001.html
>
> ------------------------------
>
> Message: 9
> Date: Mon, 02 Apr 2007 11:13:02 -0400
> From: Michael Frumin <mfrumin at rpa.org>
> Subject: Re: [postgis-users] SRID for analyzing a USA national data
> set in Meters
> To: postgis-users at postgis.refractions.net
> Message-ID: <46111D7E.1050100 at rpa.org>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> pedro,
>
> This appears to suggest that i should pick an SRID based on one point in
> my data set. However, since the data set is national, it includes
> points from all around the USA (i.e. at least 4 UTM zones). So, if I
> pick the SRID for, say, Mountain Time, will that horribly distort points
> on the east and/or west coast? is there no SRID that is perhaps less
> accurate in any one time zone, but more accurate across all time zones
> in the [continental] US?
>
> thanks,
> mike
>
> pdoria at netmadeira.com wrote:
> > Hello Michael,
> >
> > You determine the correct SRID with this bit of code (PHP):
> > (the $loc was obtained from a SELECT with the astext(geometry) function)
> >
> > The idea behind this is summarized in the UtmZoneFromLong() function.
> > Pick a Long value and calculate the utm zone for it.
> > Then use the SridFromUtmZone() function to get the srid from the given mask
> > (see below) --> this queries spatial_ref_sys with the calculated $utm param.
> > SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS 84 / UTM
> > zone $utm%' LIMIT 1
> >
> > // extract the POINT geometry
> > $geometry = str_replace("POINT(","", $loc);
> > $geometry = str_replace(")","",$geometry);
> > $coordinate = explode(" ",$geometry);
> > $lon=$coordinate[0];
> > $lat=$coordinate[1];
> >
> > // get utm zone from coordinates
> > $utm=UtmZoneFromLong($lon);
> > // get srid from the spatial_ref_sys table for the given utm zone
> > // add N or S
> > if($lat>=0) $utm.="N";
> > else $utm.="S";
> > $srid=SridFromUtmZone($utm, $connection);
> >
> >
> > // determine UTM zone from LonLat coordinates
> > // params $long: longitude
> > // returns (int) utm
> > function UtmZoneFromLong($lon) {
> > $utm = floor(($lon + 180) / 6) + 1;
> > return $utm;
> > }
> >
> > // query the spatial_ref_sys table for the srid of the given utm zone
> > // params $utm: utm zone string (MUST BE TRAILED WITH 'N' OR 'S'!), $conn:
> > connection to the db
> > // returns (int) srid
> > function SridFromUtmZone($utm, $conn){
> > $myresult = pg_exec($conn, "SELECT srid FROM spatial_ref_sys WHERE srtext
> > LIKE 'PROJCS[\"WGS 84 / UTM zone $utm%' LIMIT 1");
> > $srid=pg_result($myresult, 0, 0);
> > return $srid;
> > }
> >
> > HTH,
> > Pedro Doria Meunier
> >
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of mfrumin
> > Sent: segunda-feira, 2 de Abril de 2007 14:12
> > To: postgis-users at postgis.refractions.net
> > Subject: [postgis-users] SRID for analyzing a USA national data set in
> > Meters
> >
> >
> > I have a data set that includes points all over the United States in lat/lng
> > (say, SRID = 4326) but I need to do some analysis on these data in real
> > units, say meters. So, my task is to select an appropriate SRID that will
> > work reasonably well for the whole USA to reproject lat/lng into meters. There are a bazillion SRID's available. Any suggestions?
> >
> > This analysis doesn't have to be super-precise, so I'm not so concerned with
> > the limitations of a national projection (as opposed to state/region).
> >
> > thanks,
> > Michael
> >
> > -- View this message in context:
> > http://www.nabble.com/SRID-for-analyzing-a-USA-national-data-set-in-Meters-t
> > f3505664.html#a9790471
> > Sent from the PostGIS - User mailing list archive at Nabble.com.
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> ------------------------------
>
> Message: 10
> Date: Mon, 02 Apr 2007 11:25:44 -0400
> From: Stephen Woodbridge <woodbri at swoodbridge.com>
> Subject: Re: [postgis-users] SRID for analyzing a USA national data
> set in Meters
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <46112078.1090506 at swoodbridge.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Michael,
>
> The Simple answer is that there is no simple answer. You have not given
> us enough information about what kind of calculations you need to do and
> what are acceptable errors. Are you worried about distortions in
> distance, area, or direction/heading for example.
>
> Why don't you do all you analysis in lat/lon and convert to meters based
> on the fact that 1 deg is roughly 69..... nautical miles and you can
> convert miles to meters? You this would be problematic over large
> changes in latitude. Or use distance_sphere() which uses another
> approximation based on a spherical earth.
>
> Any suggestion we can offer will have problems. You need to look at the
> problems in light of you analysis constraints and pick a compomise that
> you can live with. If you give us additional information me might be
> able to make additional suggestions.
>
> Also variants on this request can be found in the archives as this is a
> fairly common question.
>
> Hope this helps,
> -Steve W
>
> Michael Frumin wrote:
> > pedro,
> >
> > This appears to suggest that i should pick an SRID based on one point in
> > my data set. However, since the data set is national, it includes
> > points from all around the USA (i.e. at least 4 UTM zones). So, if I
> > pick the SRID for, say, Mountain Time, will that horribly distort points
> > on the east and/or west coast? is there no SRID that is perhaps less
> > accurate in any one time zone, but more accurate across all time zones
> > in the [continental] US?
> >
> > thanks,
> > mike
> >
> > pdoria at netmadeira.com wrote:
> >> Hello Michael,
> >>
> >> You determine the correct SRID with this bit of code (PHP):
> >> (the $loc was obtained from a SELECT with the astext(geometry) function)
> >>
> >> The idea behind this is summarized in the UtmZoneFromLong() function.
> >> Pick a Long value and calculate the utm zone for it.
> >> Then use the SridFromUtmZone() function to get the srid from the given
> >> mask
> >> (see below) --> this queries spatial_ref_sys with the calculated $utm
> >> param.
> >> SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS 84 / UTM
> >> zone $utm%' LIMIT 1
> >>
> >> // extract the POINT geometry
> >> $geometry = str_replace("POINT(","", $loc);
> >> $geometry = str_replace(")","",$geometry);
> >> $coordinate = explode(" ",$geometry);
> >> $lon=$coordinate[0];
> >> $lat=$coordinate[1];
> >>
> >> // get utm zone from coordinates
> >> $utm=UtmZoneFromLong($lon);
> >> // get srid from the spatial_ref_sys table for the given utm zone
> >> // add N or S
> >> if($lat>=0) $utm.="N";
> >> else $utm.="S";
> >> $srid=SridFromUtmZone($utm, $connection);
> >>
> >>
> >> // determine UTM zone from LonLat coordinates
> >> // params $long: longitude
> >> // returns (int) utm
> >> function UtmZoneFromLong($lon) {
> >> $utm = floor(($lon + 180) / 6) + 1;
> >> return $utm;
> >> }
> >>
> >> // query the spatial_ref_sys table for the srid of the given utm zone
> >> // params $utm: utm zone string (MUST BE TRAILED WITH 'N' OR 'S'!),
> >> $conn:
> >> connection to the db
> >> // returns (int) srid
> >> function SridFromUtmZone($utm, $conn){
> >> $myresult = pg_exec($conn, "SELECT srid FROM spatial_ref_sys WHERE srtext
> >> LIKE 'PROJCS[\"WGS 84 / UTM zone $utm%' LIMIT 1");
> >> $srid=pg_result($myresult, 0, 0);
> >> return $srid;
> >> }
> >>
> >> HTH,
> >> Pedro Doria Meunier
> >>
> >> -----Original Message-----
> >> From: postgis-users-bounces at postgis.refractions.net
> >> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> >> mfrumin
> >> Sent: segunda-feira, 2 de Abril de 2007 14:12
> >> To: postgis-users at postgis.refractions.net
> >> Subject: [postgis-users] SRID for analyzing a USA national data set in
> >> Meters
> >>
> >>
> >> I have a data set that includes points all over the United States in
> >> lat/lng
> >> (say, SRID = 4326) but I need to do some analysis on these data in real
> >> units, say meters. So, my task is to select an appropriate SRID that
> >> will
> >> work reasonably well for the whole USA to reproject lat/lng into
> >> meters. There are a bazillion SRID's available. Any suggestions?
> >>
> >> This analysis doesn't have to be super-precise, so I'm not so
> >> concerned with
> >> the limitations of a national projection (as opposed to state/region).
> >>
> >> thanks,
> >> Michael
> >>
> >> -- View this message in context:
> >> http://www.nabble.com/SRID-for-analyzing-a-USA-national-data-set-in-Meters-t
> >>
> >> f3505664.html#a9790471
> >> Sent from the PostGIS - User mailing list archive at Nabble.com.
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 11
> Date: Mon, 2 Apr 2007 16:30:18 +0100
> From: "Pedro Doria Meunier" <pdoria at netmadeira.com>
> Subject: RE: [postgis-users] SRID for analyzing a USA national data
> set in Meters
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <002f01c7753b$d2aa60d0$77ff2270$@com>
> Content-Type: text/plain; charset="us-ascii"
>
> Hey Mike,
>
> The only global system, adopted by 1984, is WGS84 (this has been revised in
> 2004 and it's valid until 2010).
> This, as you well know, is a system whose units are expressed in degrees.
> If you wish to work in metres you must use a 'localized' system (projected).
> The correct geoid must be used for accurate calculations.
>
> So my advice to you is defining 'work areas' (as in regions) if you really
> need to work with metres..
>
> This comes from my notes:
> "an inaccurate way for calculating the distance would be:
> distance [m] = 6378137.0 [m] * Pi * distance [degree] / 180.0
> 6378137.0 = earth radius
>
> accurate measurement involves the correct geoid to be used (UTM projection)"
>
> INACURRACY ALARM! The above method is horribly inaccurate...
>
> Depending on your task you can programmatically (as in using PHP or whatever
> lang) process your points.
> Could you elaborate on the task at hand?
>
> Pedro.
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Michael
> Frumin
> Sent: segunda-feira, 2 de Abril de 2007 16:13
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] SRID for analyzing a USA national data set in
> Meters
>
> pedro,
>
> This appears to suggest that i should pick an SRID based on one point in
> my data set. However, since the data set is national, it includes
> points from all around the USA (i.e. at least 4 UTM zones). So, if I
> pick the SRID for, say, Mountain Time, will that horribly distort points
> on the east and/or west coast? is there no SRID that is perhaps less
> accurate in any one time zone, but more accurate across all time zones
> in the [continental] US?
>
> thanks,
> mike
>
>
>
>
> ------------------------------
>
> Message: 12
> Date: Mon, 02 Apr 2007 08:35:19 -0700
> From: Paul Ramsey <pramsey at refractions.net>
> Subject: Re: [postgis-users] SRID for analyzing a USA national data
> set in Meters
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <461122B7.7010808 at refractions.net>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Pedro Doria Meunier wrote:
> > Hey Mike,
> >
> > The only global system, adopted by 1984, is WGS84 (this has been revised in
> > 2004 and it's valid until 2010).
> > This, as you well know, is a system whose units are expressed in degrees.
> > If you wish to work in metres you must use a 'localized' system (projected).
> > The correct geoid must be used for accurate calculations.
> >
> > So my advice to you is defining 'work areas' (as in regions) if you really
> > need to work with metres..
> >
> > This comes from my notes:
> > "an inaccurate way for calculating the distance would be:
> > distance [m] = 6378137.0 [m] * Pi * distance [degree] / 180.0
> > 6378137.0 = earth radius
>
> Use distance_sphere() to get this as a built-in postgis function.
> Distance_spheroid() returns an accurate distance, but at 10 times the
> CPU cost of the sphere calc.
>
> P
>
> >
> > accurate measurement involves the correct geoid to be used (UTM projection)"
> >
> > INACURRACY ALARM! The above method is horribly inaccurate...
> >
> > Depending on your task you can programmatically (as in using PHP or whatever
> > lang) process your points.
> > Could you elaborate on the task at hand?
> >
> > Pedro.
> >
> >
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Michael
> > Frumin
> > Sent: segunda-feira, 2 de Abril de 2007 16:13
> > To: postgis-users at postgis.refractions.net
> > Subject: Re: [postgis-users] SRID for analyzing a USA national data set in
> > Meters
> >
> > pedro,
> >
> > This appears to suggest that i should pick an SRID based on one point in
> > my data set. However, since the data set is national, it includes
> > points from all around the USA (i.e. at least 4 UTM zones). So, if I
> > pick the SRID for, say, Mountain Time, will that horribly distort points
> > on the east and/or west coast? is there no SRID that is perhaps less
> > accurate in any one time zone, but more accurate across all time zones
> > in the [continental] US?
> >
> > thanks,
> > mike
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> --
>
> Paul Ramsey
> Refractions Research
> http://www.refractions.net
> pramsey at refractions.net
> Phone: 250-383-3022
> Cell: 250-885-0632
>
>
> ------------------------------
>
> Message: 13
> Date: Mon, 02 Apr 2007 11:39:09 -0400
> From: Michael Frumin <mfrumin at rpa.org>
> Subject: Re: [postgis-users] SRID for analyzing a USA national data
> set in Meters
> To: postgis-users at postgis.refractions.net
> Message-ID: <4611239D.6000403 at rpa.org>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Right, I should be more specific from the outset. I did do some
> searching thru the PostGIS archives, and didn't find the answer I was
> looking for; is there a PostGIS FAQ somewhere?
>
> As for my problem, my inputs are two sets of geocoded hospitals, and I
> just want to be able to identify for each hospital in the first set, the
> hospitals in the second set within approximately 25 miles. I will the
> map these sets, with a 25 mile buffer around the first set, using
> Geoserver. So, distance and area are both somewhat important, heading
> not at all. distance_sphere(oid), sounds good for the calculation, but
> won't help with the buffering because it doesn't tell me the 'distance'
> in lat/lng space that would equate to 25 miles (because of course this
> varies over the globe). To achieve this I would need to reproject into
> something that is in meters, and buffer around that.
>
> How egregious would you expect the errors to be if I simply use the
> projection for the UTM zone that represents, say, Central time?
>
> thanks,
> mike
>
> woodbri at swoodbridge.com wrote:
> > Michael,
> >
> > The Simple answer is that there is no simple answer. You have not given us enough information about what kind of calculations you need to do and what are acceptable errors. Are you worried about distortions in distance, area, or direction/heading for example.
> >
> > Why don't you do all you analysis in lat/lon and convert to meters based on the fact that 1 deg is roughly 69..... nautical miles and you can convert miles to meters? You this would be problematic over large changes in latitude. Or use distance_sphere() which uses another approximation based on a spherical earth.
> >
> > Any suggestion we can offer will have problems. You need to look at the problems in light of you analysis constraints and pick a compomise that you can live with. If you give us additional information me might be able to make additional suggestions.
> >
> > Also variants on this request can be found in the archives as this is a fairly common question.
> >
> > Hope this helps,
> > -Steve W
> >
> > Michael Frumin wrote:
> >
> >> pedro,
> >>
> >>> This appears to suggest that i should pick an SRID based on one point in > my data set. However, since the data set is national, it includes > points from all around the USA (i.e. at least 4 UTM zones). So, if I > pick the SRID for, say, Mountain Time, will that horribly distort points > on the east and/or west coast? is there no SRID that is perhaps less > accurate in any one time zone, but more accurate across all time zones > in the [continental] US?
> >>> thanks,
> >>>
> >> mike
> >>
> >>> pdoria at netmadeira.com wrote:
> >>> Hello Michael,
> >>>
> >>> You determine the correct SRID with this bit of code (PHP):
> >>> (the $loc was obtained from a SELECT with the astext(geometry) function)
> >>>
> >>> The idea behind this is summarized in the UtmZoneFromLong() function.
> >>> Pick a Long value and calculate the utm zone for it.
> >>> Then use the SridFromUtmZone() function to get the srid from the given >> mask
> >>> (see below) --> this queries spatial_ref_sys with the calculated $utm >> param.
> >>> SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS 84 / UTM
> >>> zone $utm%' LIMIT 1
> >>>
> >>> // extract the POINT geometry
> >>> $geometry = str_replace("POINT(","", $loc);
> >>> $geometry = str_replace(")","",$geometry);
> >>> $coordinate = explode(" ",$geometry);
> >>> $lon=$coordinate[0];
> >>> $lat=$coordinate[1];
> >>>
> >>> // get utm zone from coordinates
> >>> $utm=UtmZoneFromLong($lon);
> >>> // get srid from the spatial_ref_sys table for the given utm zone
> >>> // add N or S
> >>> if($lat>=0) $utm.="N";
> >>> else $utm.="S";
> >>> $srid=SridFromUtmZone($utm, $connection);
> >>>
> >>>
> >>> // determine UTM zone from LonLat coordinates
> >>> // params $long: longitude
> >>> // returns (int) utm
> >>> function UtmZoneFromLong($lon) {
> >>> $utm = floor(($lon + 180) / 6) + 1;
> >>> return $utm;
> >>> }
> >>>
> >>> // query the spatial_ref_sys table for the srid of the given utm zone
> >>> // params $utm: utm zone string (MUST BE TRAILED WITH 'N' OR 'S'!), >> $conn:
> >>> connection to the db
> >>> // returns (int) srid
> >>> function SridFromUtmZone($utm, $conn){
> >>> $myresult = pg_exec($conn, "SELECT srid FROM spatial_ref_sys WHERE srtext
> >>> LIKE 'PROJCS[\"WGS 84 / UTM zone $utm%' LIMIT 1");
> >>> $srid=pg_result($myresult, 0, 0);
> >>> return $srid;
> >>> }
> >>>
> >>> HTH,
> >>> Pedro Doria Meunier
> >>>
> >>> -----Original Message-----
> >>> From: postgis-users-bounces at postgis.refractions.net
> >>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of >> mfrumin
> >>> Sent: segunda-feira, 2 de Abril de 2007 14:12
> >>> To: postgis-users at postgis.refractions.net
> >>> Subject: [postgis-users] SRID for analyzing a USA national data set in
> >>> Meters
> >>>
> >>>
> >>> I have a data set that includes points all over the United States in >> lat/lng
> >>> (say, SRID = 4326) but I need to do some analysis on these data in real
> >>> units, say meters. So, my task is to select an appropriate SRID that >> will
> >>> work reasonably well for the whole USA to reproject lat/lng into >> meters. There are a bazillion SRID's available. Any suggestions?
> >>>
> >>> This analysis doesn't have to be super-precise, so I'm not so >> concerned with
> >>> the limitations of a national projection (as opposed to state/region).
> >>>
> >>> thanks,
> >>> Michael
> >>>
> >>> -- View this message in context:
> >>> http://www.nabble.com/SRID-for-analyzing-a-USA-national-data-set-in-Meters-t >>
> >>> f3505664.html#a9790471
> >>> Sent from the PostGIS - User mailing list archive at Nabble.com.
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>> > _______________________________________________
> >>>
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20070402/6b662808/attachment-0001.html
>
> ------------------------------
>
> Message: 14
> Date: Mon, 02 Apr 2007 09:58:30 -0700
> From: Paul Ramsey <pramsey at refractions.net>
> Subject: Re: [postgis-users] Indexes not being used
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <46113636.2030604 at refractions.net>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> It's running in 1 millisecond, what's the problem? If your tables are
> small, it will ignore the indexes because the cost of sequence scanning
> is lower than the index scan cost.
>
> P
>
> Pedro Doria Meunier wrote:
> > Hi all (with a special wink to Regina ;- )
> >
> > This is the query not using indices:
> >
> > SELECT parish,county,geometry FROM pt_madeira_toponymy as t WHERE
> > parish=upper('se') AND
> >
> > t.geometry && geomfromtext('POINT(-16.9213592631455
> > 32.6437878212273)',4326) AND
> > intersects(geomfromtext('POINT(-16.9213592631455
> > 32.6437878212273)',4326), t.geometry);
> >
> > As you can plainly see it checks if a point is inside some polygon.
> >
> >
> >
> > EXPLAIN ANALYZE returns this:
> >
> > "Seq Scan on pt_madeira_toponymy t (cost=0.00..3.03 rows=1 width=96)
> > (actual time=1.086..1.278 rows=1 loops=1)"
> >
> > " Filter: ((parish = 'SE'::text) AND (geometry &&
> > '0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry) AND
> > intersects('0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry,
> > geometry))"
> >
> > "Total runtime: 1.312 ms"
> >
>
> > The toponymy has two indices: one for 4326 and another for 32628 using GiST.
>
> >
> > Is the intersects function not using indices at all??
>
> --
>
> Paul Ramsey
> Refractions Research
> http://www.refractions.net
> pramsey at refractions.net
> Phone: 250-383-3022
> Cell: 250-885-0632
>
>
> ------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 54, Issue 2
> ********************************************
>
More information about the postgis-users
mailing list