If you're trying to geocode an address, you'll have to interpolate over the linestring you've determined to contain the target address. Luckily, the nice developers of PostGIS have provided you with the line_interpolate_point function.
<br><br>Now, you may run into the situation one day where you have MULTILINESTRING as the type. Here is an example of using this function. In my case, I am finding the closest point out of all linestrings in my line to a target point. In other words, this is part of a reverse geocoder.
<br><br><span style="font-family: courier new,monospace;">drop type interpolated_point cascade;</span><br style="font-family: courier new,monospace;"><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
create type interpolated_point as (</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> point geometry,</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
percent numeric</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">);</span><br style="font-family: courier new,monospace;"><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">create or replace function getPercentageDownLine (geometry, geometry) returns interpolated_point as $$</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
declare</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> tmp numeric := 0;</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
percent numeric := 1;</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> rec interpolated_point;</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
geom geometry;</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">begin</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">
for i in 1..NumGeometries($2) loop</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> tmp := line_locate_point(GeometryN($2, i), $1);</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> if tmp < percent then</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> percent := tmp;</span>
<br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> geom := line_interpolate_point(GeometryN($2, i), percent);</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> end if;</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> end loop;</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> select geom, percent into rec;</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> return rec;</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">end;</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">$$ language plpgsql;</span><br style="font-family: courier new,monospace;">
<br><br><div><span class="gmail_quote">On 2/12/07, <b class="gmail_sendername"><a href="mailto:postgis-users-request@postgis.refractions.net">postgis-users-request@postgis.refractions.net</a></b> <<a href="mailto:postgis-users-request@postgis.refractions.net">
postgis-users-request@postgis.refractions.net</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Send postgis-users mailing list submissions to
<br> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><br>To subscribe or unsubscribe via the World Wide Web, visit<br> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>or, via email, send a message with subject or body 'help' to<br> <a href="mailto:postgis-users-request@postgis.refractions.net">postgis-users-request@postgis.refractions.net
</a><br><br>You can reach the person managing the list at<br> <a href="mailto:postgis-users-owner@postgis.refractions.net">postgis-users-owner@postgis.refractions.net</a><br><br>When replying, please edit your Subject line so it is more specific
<br>than "Re: Contents of postgis-users digest..."<br><br><br>Today's Topics:<br><br> 1. distance from imported tiger database (mark)<br> 2. Re: Re: postgis-users Digest, Vol 52, Issue 9 (Ragi Y. Burhum)
<br> 3. Re: Re: postgis-users Digest, Vol 52, Issue 9 (Tim Bowden)<br> 4. latitude / longitude from wkb_geometry (mark)<br> 5. RE: latitude / longitude from wkb_geometry (Pedro Doria Meunier)<br> 6. PostGIS with Bentley Geospatial Solution (Dionnald boonheng)
<br> 7. RE: Spatial Queries on Postgis Java Geometry classes<br> (Norman Barker)<br> 8. RE: latitude / longitude from wkb_geometry (Pedro Doria Meunier)<br> 9. RE: latitude / longitude from wkb_geometry (Pedro Doria Meunier)
<br> 10. RE: within_distance function (Obe, Regina)<br> 11. RE: distance from imported tiger database (Obe, Regina)<br> 12. RE: distance from imported tiger database (Obe, Regina)<br> 13. Re: within_distance function (Paul Ramsey)
<br> 14. appending to a LINESTRING (Just van den Broecke)<br> 15. RE: within_distance function (Obe, Regina)<br> 16. Re: latitude / longitude from wkb_geometry (mark)<br> 17. Re: distance from imported tiger database (mark)
<br> 18. RE: latitude / longitude from wkb_geometry (Pedro Doria Meunier)<br> 19. Re: appending to a LINESTRING (David William Bitner)<br> 20. Re: latitude / longitude from wkb_geometry (mark)<br> 21. Re: latitude / longitude from wkb_geometry (Emily Gouge)
<br> 22. RE: latitude / longitude from wkb_geometry (Pedro Doria Meunier)<br> 23. RE: latitude / longitude from wkb_geometry (Pedro Doria Meunier)<br> 24. Re: latitude / longitude from wkb_geometry (mark)<br> 25. RE: latitude / longitude from wkb_geometry (Pedro Doria Meunier)
<br> 26. Re: latitude / longitude from wkb_geometry (mark)<br> 27. RE: latitude / longitude from wkb_geometry (Obe, Regina)<br> 28. Re: latitude / longitude from wkb_geometry (mark)<br> 29. Re: latitude / longitude from wkb_geometry (Paul Ramsey)
<br><br><br>----------------------------------------------------------------------<br><br>Message: 1<br>Date: Sun, 11 Feb 2007 18:02:31 -0800<br>From: mark <<a href="mailto:rkmr.em@gmail.com">rkmr.em@gmail.com</a>><br>
Subject: [postgis-users] distance from imported tiger database<br>To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>Message-ID:<br> <<a href="mailto:e04d2e90702111802n36513682i8217dea7668de134@mail.gmail.com">
e04d2e90702111802n36513682i8217dea7668de134@mail.gmail.com</a>><br>Content-Type: text/plain; charset="iso-8859-1"<br><br>Hi<br>I imported the tiger database with the ogr tool. It has created table with<br>the structure below.
<br>geometry_columns has an entry like this:<br><br>197276;"''";"public";"masuf";"wkb_geometry";"2";"4269";"LINESTRING"<br><br>Can you tell how I can query for locations based on distance using these?
<br>thanks a lot<br>mark<br><br>CREATE TABLE masuf<br>(<br>ogc_fid serial NOT NULL,<br>wkb_geometry geometry,<br>module char(8),<br>tlid numeric(10),<br>side1 numeric(1),<br>source char(1),<br>fedirp char(2),<br>fename char(30),
<br>fetype char(4),<br>fedirs char(2),<br>cfcc char(3),<br>fraddl char(11),<br>toaddl char(11),<br>fraddr char(11),<br>toaddr char(11),<br>friaddl char(1),<br>toiaddl char(1),<br>friaddr char(1),<br>toiaddr char(1),<br>zipl numeric(5),
<br>zipr numeric(5),<br>aianhhfpl numeric(5),<br>aianhhfpr numeric(5),<br>aihhtlil char(1),<br>aihhtlir char(1),<br>census1 char(1),<br>census2 char(1),<br>statel numeric(2),<br>stater numeric(2),<br>countyl numeric(3),<br>
countyr numeric(3),<br>cousubl numeric(5),<br>cousubr numeric(5),<br>submcdl numeric(5),<br>submcdr numeric(5),<br>placel numeric(5),<br>placer numeric(5),<br>tractl numeric(6),<br>tractr numeric(6),<br>blockl numeric(4),
<br>blockr numeric(4),<br>CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>CONSTRAINT enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) =<br>
'LINESTRING'::text OR wkb_geometry IS NULL),<br>CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>)<br><br><br>On 2/11/07, mark <<a href="mailto:rkmr.em@gmail.com">rkmr.em@gmail.com</a>> wrote:
<br>><br>> hi<br>> are there any tools for importing the tiger database into postgres to use<br>> with postgis?<br>> thanks !<br>> mark<br>><br>-------------- next part --------------<br>An HTML attachment was scrubbed...
<br>URL: <a href="http://lists.refractions.net/pipermail/postgis-users/attachments/20070211/09cb42b3/attachment-0001.html">http://lists.refractions.net/pipermail/postgis-users/attachments/20070211/09cb42b3/attachment-0001.html
</a><br><br>------------------------------<br><br>Message: 2<br>Date: Sun, 11 Feb 2007 18:55:48 -0800<br>From: "Ragi Y. Burhum" <<a href="mailto:ragi@burhum.com">ragi@burhum.com</a>><br>Subject: Re: [postgis-users] Re: postgis-users Digest, Vol 52, Issue 9
<br>To: "Frank Koormann" <<a href="mailto:frank.koormann@intevation.de">frank.koormann@intevation.de</a>><br>Cc: PostGIS Users Discussion <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a>><br>Message-ID:<br> <<a href="mailto:738927440702111855g4cb7b890ub0565ec571f93533@mail.gmail.com">738927440702111855g4cb7b890ub0565ec571f93533@mail.gmail.com</a>><br>Content-Type: text/plain; charset="iso-8859-1"
<br><br>On 2/11/07, Frank Koormann <<a href="mailto:frank.koormann@intevation.de">frank.koormann@intevation.de</a>> wrote:<br>><br>> * Ragi Y. Burhum <<a href="mailto:ragi@burhum.com">ragi@burhum.com</a>> [070210 08:01]:
<br>> > > ><br>> > > > ZigGis 1.1 available!<br>> > > ><br>> > ><br>> [7]http://postgis.refractions.net/pipermail/postgis-users/2006-December/014217.html<br>> > > > HTH,
<br>> > > ><br>> > > > Frank Koormann<br>> > > ><br>> > ><br>> > > It's still alive! Looks more promising. Thanks Frank. It's a read<br>> only<br>
> > > job for now, but still useful.<br>><br>> > As far as commercial products, you can use the Data Interoperability<br>> > Extension<br>><br>> > [8]http://www.esri.com/software/arcgis/extensions/datainteroperability/index.html
<br>> > for Read/Write.<br>><br>><br>> Has anybody used this "Write" already or is it just a legend?<br>> Looking at the "Data Interoperability" sheet [1] I find<br>> columns for Direct Read, Data Import and Data Export. No Direct Write.
<br>><br>> Regards,<br>><br>> Frank Koormann<br>><br>><br>> [1] <a href="http://www.esri.com/library/fliers/pdfs/data-interop-formats.pdf">http://www.esri.com/library/fliers/pdfs/data-interop-formats.pdf
</a><br>><br>> --<br>> Frank Koormann <<a href="mailto:frank.koormann@intevation.de">frank.koormann@intevation.de</a>><br>> Professional Service around Free Software (<a href="http://intevation.net/">
http://intevation.net/</a>)<br>> FreeGIS Project (<a href="http://freegis.org/">http://freegis.org/</a>)<br>> PostGIS Support (<br>> <a href="http://www.intevation.net/geospatial/postgis-support.en.html">
http://www.intevation.net/geospatial/postgis-support.en.html</a>)<br>><br><br><br>I haven't personally used it for editing Postgis (I have for other<br>datasources), however Safe's FME (which are the libraries used by Data
<br>Interop Extension)<br><a href="http://www.esri.com/industries/electric/resources/data/safesoftware.htmllists">http://www.esri.com/industries/electric/resources/data/safesoftware.htmllists</a><br>Postgis as read/write<br>
<a href="http://www.safe.com/products/fme/formats-supported/index.php">http://www.safe.com/products/fme/formats-supported/index.php</a> . You may want<br>to double check with FME and/or ESRI to see what's the story.<br>
<br>Hope this helps.<br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <a href="http://lists.refractions.net/pipermail/postgis-users/attachments/20070211/10b086c9/attachment-0001.html">
http://lists.refractions.net/pipermail/postgis-users/attachments/20070211/10b086c9/attachment-0001.html</a><br><br>------------------------------<br><br>Message: 3<br>Date: Mon, 12 Feb 2007 16:11:23 +1100<br>From: Tim Bowden <
<a href="mailto:tim.bowden@westnet.com.au">tim.bowden@westnet.com.au</a>><br>Subject: Re: [postgis-users] Re: postgis-users Digest, Vol 52, Issue 9<br>To: PostGIS Users Discussion <<a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a>><br>Message-ID: <1171257083.5179.1.camel@localhost><br>Content-Type: text/plain<br><br>On Sun, 2007-02-11 at 18:55 -0800, Ragi Y. Burhum wrote:<br>> On 2/11/07, Frank Koormann <
<a href="mailto:frank.koormann@intevation.de">frank.koormann@intevation.de</a>> wrote:<br>> * Ragi Y. Burhum <<a href="mailto:ragi@burhum.com">ragi@burhum.com</a>> [070210 08:01]:<br>> > > >
<br>> > > > ZigGis 1.1 available!<br>> > > ><br>> > ><br>> [7]http://postgis.refractions.net/pipermail/postgis-users/2006-December/014217.html<br>> > > > HTH,
<br>> > > ><br>> > > > Frank Koormann<br>> > > ><br>> > ><br>> > > It's still alive! Looks more promising. Thanks<br>
> Frank. It's a read only<br>> > > job for now, but still useful.<br>><br>> > As far as commercial products, you can use the Data<br>> Interoperability<br>> > Extension
<br>> > [8]http://www.esri.com/software/arcgis/extensions/datainteroperability/index.html<br>> > for Read/Write.<br>><br>><br>> Has anybody used this "Write" already or is it just a legend?
<br>> Looking at the "Data Interoperability" sheet [1] I find<br>> columns for Direct Read, Data Import and Data Export. No<br>> Direct Write.<br>><br>> Regards,<br>
><br>> Frank Koormann<br>><br>><br>> [1]<br>> <a href="http://www.esri.com/library/fliers/pdfs/data-interop-formats.pdf">http://www.esri.com/library/fliers/pdfs/data-interop-formats.pdf
</a><br>><br>> --<br>> Frank Koormann <<br>> <a href="mailto:frank.koormann@intevation.de">frank.koormann@intevation.de</a>><br>> Professional Service around Free Software
<br>> (<a href="http://intevation.net/">http://intevation.net/</a>)<br>> FreeGIS Project<br>> ( <a href="http://freegis.org/">http://freegis.org/</a>)<br>> PostGIS Support<br>> (
<a href="http://www.intevation.net/geospatial/postgis-support.en.html">http://www.intevation.net/geospatial/postgis-support.en.html</a>)<br>><br>><br>> I haven't personally used it for editing Postgis (I have for other
<br>> datasources), however Safe's FME (which are the libraries used by<br>> Data Interop Extension)<br>> <a href="http://www.esri.com/industries/electric/resources/data/safesoftware.html">http://www.esri.com/industries/electric/resources/data/safesoftware.html
</a> lists Postgis as read/write <a href="http://www.safe.com/products/fme/formats-supported/index.php">http://www.safe.com/products/fme/formats-supported/index.php</a> . You may want to double check with FME and/or ESRI to see what's the story.
<br>><br>> Hope this helps.<br>Thanks,<br><br>This is something we will have to look at. It's a pity there's no<br>comparable OS option :-(<br><br>Regards,<br>Tim Bowden<br><br><br><br>------------------------------
<br><br>Message: 4<br>Date: Sun, 11 Feb 2007 21:57:20 -0800<br>From: mark <<a href="mailto:rkmr.em@gmail.com">rkmr.em@gmail.com</a>><br>Subject: [postgis-users] latitude / longitude from wkb_geometry<br>To: <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>Message-ID:<br> <<a href="mailto:e04d2e90702112157i1f267334o2dfcc9c38a88d038@mail.gmail.com">e04d2e90702112157i1f267334o2dfcc9c38a88d038@mail.gmail.com</a>><br>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
<br><br>how to get latitude and longitude from wkb_geometry?<br>My table structure is given below<br>thanks<br>mark<br><br>CREATE TABLE masuf<br>(<br>ogc_fid serial NOT NULL,<br>wkb_geometry geometry,<br>module char(8),<br>
tlid numeric(10),<br>side1 numeric(1),<br>source char(1),<br>fedirp char(2),<br>fename char(30),<br>fetype char(4),<br>fedirs char(2),<br>cfcc char(3),<br>fraddl char(11),<br>toaddl char(11),<br>fraddr char(11),<br>toaddr char(11),
<br>friaddl char(1),<br>toiaddl char(1),<br>friaddr char(1),<br>toiaddr char(1),<br>zipl numeric(5),<br>zipr numeric(5),<br>aianhhfpl numeric(5),<br>aianhhfpr numeric(5),<br>aihhtlil char(1),<br>aihhtlir char(1),<br>census1 char(1),
<br>census2 char(1),<br>statel numeric(2),<br>stater numeric(2),<br>countyl numeric(3),<br>countyr numeric(3),<br>cousubl numeric(5),<br>cousubr numeric(5),<br>submcdl numeric(5),<br>submcdr numeric(5),<br>placel numeric(5),
<br>placer numeric(5),<br>tractl numeric(6),<br>tractr numeric(6),<br>blockl numeric(4),<br>blockr numeric(4),<br>CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),
<br>CONSTRAINT enforce_geotype_wkb_geometry CHECK<br>(geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>NULL),<br>CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>)<br>
<br><br>------------------------------<br><br>Message: 5<br>Date: Mon, 12 Feb 2007 07:47:00 -0000<br>From: "Pedro Doria Meunier" <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>><br>Subject: RE: [postgis-users] latitude / longitude from wkb_geometry
<br>To: "'PostGIS Users Discussion'"<br> <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: <001801c74e7a$05e3f2b0$11abd810$@com
><br>Content-Type: text/plain; charset="us-ascii"<br><br>Hi Mark<br><br>There's a number of ways... Here's one:<br><br>SELECT x(wkb_geometry), y(wkb_geometry) from masuf;<br><br>HTH<br>Pedro Doria Meunier
<br><br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>Sent: segunda-feira, 12 de Fevereiro de 2007 5:57<br>To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br>Subject: [postgis-users] latitude / longitude from wkb_geometry<br><br>how to get latitude and longitude from wkb_geometry?<br>My table structure is given below<br>thanks<br>mark<br><br>CREATE TABLE masuf<br>(<br>
ogc_fid serial NOT NULL,<br>wkb_geometry geometry,<br>module char(8),<br>tlid numeric(10),<br>side1 numeric(1),<br>source char(1),<br>fedirp char(2),<br>fename char(30),<br>fetype char(4),<br>fedirs char(2),<br>cfcc char(3),
<br>fraddl char(11),<br>toaddl char(11),<br>fraddr char(11),<br>toaddr char(11),<br>friaddl char(1),<br>toiaddl char(1),<br>friaddr char(1),<br>toiaddr char(1),<br>zipl numeric(5),<br>zipr numeric(5),<br>aianhhfpl numeric(5),
<br>aianhhfpr numeric(5),<br>aihhtlil char(1),<br>aihhtlir char(1),<br>census1 char(1),<br>census2 char(1),<br>statel numeric(2),<br>stater numeric(2),<br>countyl numeric(3),<br>countyr numeric(3),<br>cousubl numeric(5),<br>
cousubr numeric(5),<br>submcdl numeric(5),<br>submcdr numeric(5),<br>placel numeric(5),<br>placer numeric(5),<br>tractl numeric(6),<br>tractr numeric(6),<br>blockl numeric(4),<br>blockr numeric(4),<br>CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),
<br>CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>CONSTRAINT enforce_geotype_wkb_geometry CHECK<br>(geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>NULL),<br>CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)
<br>)<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br><br><br>------------------------------<br><br>Message: 6<br>Date: Mon, 12 Feb 2007 16:24:13 +0800<br>From: "Dionnald boonheng" <<a href="mailto:beh.dnd@gmail.com">
beh.dnd@gmail.com</a>><br>Subject: [postgis-users] PostGIS with Bentley Geospatial Solution<br>To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>Message-ID:<br> <
<a href="mailto:52a104e20702120024x66c7362dk30fbfb3387fe2861@mail.gmail.com">52a104e20702120024x66c7362dk30fbfb3387fe2861@mail.gmail.com</a>><br>Content-Type: text/plain; charset="iso-8859-1"<br><br>Hi there,
<br><br>Good day. Is there anybody experince in using PostGIS with Bentley products<br>like Microstation, ProjectWise, Microstation GeoGraphics and so forth?<br>Kindly advise on this matter.<br><br>Is PostGIS database work with Bentley products?
<br><br>Thanks and regards,<br>Dionnald<br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <a href="http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/19519390/attachment-0001.html">
http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/19519390/attachment-0001.html</a><br><br>------------------------------<br><br>Message: 7<br>Date: Mon, 12 Feb 2007 08:55:40 -0000<br>From: "Norman Barker" <
<a href="mailto:nbarker@ittvis.com">nbarker@ittvis.com</a>><br>Subject: RE: [postgis-users] Spatial Queries on Postgis Java Geometry<br> classes<br>To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a>><br>Message-ID: <<a href="mailto:B67A5FB7A8114B47A70517C9C0D102E8131DBB@bath.uk.rsinc.com">B67A5FB7A8114B47A70517C9C0D102E8131DBB@bath.uk.rsinc.com</a>><br>Content-Type: text/plain; charset="iso-8859-1"
<br><br>Jan, Sandeep<br><br><br><br>This email just came into my inbox, but I notice you didn't say anything in it. I am not sure if this hql code got added to postgis subversion but the link is here<br><br><br><br><a href="http://article.gmane.org/gmane.comp.gis.postgis/12282/match=spatial+object+queries+postgis">
http://article.gmane.org/gmane.comp.gis.postgis/12282/match=spatial+object+queries+postgis</a><br><br><br><br><br><br>Norman<br><br><br><br>________________________________<br><br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Jan Syryn<br>Sent: 09 February 2007 15:04
<br>To: 'PostGIS Users Discussion'<br>Subject: RE: [postgis-users] Spatial Queries on Postgis Java Geometry classes<br><br><br><br><br><br><br><br>________________________________<br><br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Norman Barker<br>Sent: vendredi 1 d�cembre 2006 10:08
<br>To: PostGIS Users Discussion<br>Subject: RE: [postgis-users] Spatial Queries on Postgis Java Geometry classes<br><br><br><br><br><br>________________________________<br><br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Sandeep Kumar Jakkaraju<br>Sent: 01 December 2006 00:09
<br>To: PostGIS Users Discussion<br>Subject: [postgis-users] Spatial Queries on Postgis Java Geometry classes<br><br><br><br>Hi All<br><br>If i am correct !! all the spatial queries are done on the geometry objects ..in the table ..
<br><br>For example:-<br>I have a table ..which has a geometry column which of type say POLYGON ...say bounds<br>I want to write a spatial query ..<br><br>select * from table where intersects (GEOMFROMTEXT('POINT(0 0)'),bounds);
<br><br>but if some one is using Hibernate with Postgis ... I have to run this as a native sql query ..<br>BUT has anyone worked on Postgis ..Geometry Java CLass ..so that ..i can run a createria query<br>like<br><br>"from Tiles t where
t.bounds.intersects('POINT(0 0)')";<br><br>if someone thinks it is worthwhile to work on .. i would like to work on it ..<br><br>thanx ...<br><br>sandeep<br><br>Sandeep,<br><br>This would be useful as an object query, so EJB-QL, or HQL. I ran a demo project from Jan earlier this week with the hibernate postgis driver (so dropping all my EJB3 code) and it works fine in a hibernate/spring environment as well as JBoss. I notice that the query parser for hibernate 3 is different to
2.1, I think this may be a major piece of work - let me know if I can help.<br><br>It would be good to document any object query extensions if you have time.<br><br>Thanks,<br><br>Norman<br><br><br><br>-------------- next part --------------
<br>An HTML attachment was scrubbed...<br>URL: <a href="http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/a0844f27/attachment-0001.html">http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/a0844f27/attachment-0001.html
</a><br><br>------------------------------<br><br>Message: 8<br>Date: Mon, 12 Feb 2007 11:08:52 -0000<br>From: "Pedro Doria Meunier" <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>><br>Subject: RE: [postgis-users] latitude / longitude from wkb_geometry
<br>To: "'PostGIS Users Discussion'"<br> <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: <000c01c74e96$30c471f0$924d55d0$@com
><br>Content-Type: text/plain; charset="us-ascii"<br><br>Hi Mark<br><br>Addendum to the previous post...<br>I didn't notice that the geometry was LINESTRING :]<br>So what we need here is to concatenate some functions:
<br><br>SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf;<br><br>This will give you the first coordinate of each line.<br><br>Should you want to process all coordinates of each linestring here's a
<br>little PHP that might help:<br><br>// ------ cut here<br><?php<br>$connection = pg_connect("host=yourhost port=5432 dbname=yourdb<br>user=yourusername password=yourpassword");<br>if (!$connection) {<br> print("Connection to the database failed.");
<br> exit;<br>}<br>/*<br>Get all the records from the table.<br>We get the unique ogc_fid and the corresponding number of points for the<br>linestring of this entry...<br>*/<br>$sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";
<br>$myresult=pg_exec($connection, $sql);<br><br>for ($row=0; $row<pg_numrows($myresult); $row++) {<br> $unique=pg_result($myresult,$row,0);<br> $npoints==pg_result($myresult,$row,0);<br> // now we process each point in this entry
<br> for ($point=0; $point<$npoints; $point++) {<br> $sql= "SELECT x(pointn(wkb_geometry,$point)),<br>y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";<br> $presult=pg_exec($connection, $sql);
<br> $lon=pg_result($presult,0,0);<br> $lat=pg_result($presult,0,1);<br> /*<br> Do whatever you wish with $lon, $lat....<br> */<br> }<br>}<br>?><br>// ------ cut here<br><br><br>
If the gurus out there have a more efficient way to do this, I'd be more<br>than interested in hearing about it! ;-)<br><br>HTH,<br>Pedro Doria Meunier.<br><br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>Sent: segunda-feira, 12 de Fevereiro de 2007 5:57
<br>To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>Subject: [postgis-users] latitude / longitude from wkb_geometry<br><br>how to get latitude and longitude from wkb_geometry?
<br>My table structure is given below<br>thanks<br>mark<br><br>CREATE TABLE masuf<br>(<br>ogc_fid serial NOT NULL,<br>wkb_geometry geometry,<br>module char(8),<br>tlid numeric(10),<br>side1 numeric(1),<br>source char(1),<br>
fedirp char(2),<br>fename char(30),<br>fetype char(4),<br>fedirs char(2),<br>cfcc char(3),<br>fraddl char(11),<br>toaddl char(11),<br>fraddr char(11),<br>toaddr char(11),<br>friaddl char(1),<br>toiaddl char(1),<br>friaddr char(1),
<br>toiaddr char(1),<br>zipl numeric(5),<br>zipr numeric(5),<br>aianhhfpl numeric(5),<br>aianhhfpr numeric(5),<br>aihhtlil char(1),<br>aihhtlir char(1),<br>census1 char(1),<br>census2 char(1),<br>statel numeric(2),<br>stater numeric(2),
<br>countyl numeric(3),<br>countyr numeric(3),<br>cousubl numeric(5),<br>cousubr numeric(5),<br>submcdl numeric(5),<br>submcdr numeric(5),<br>placel numeric(5),<br>placer numeric(5),<br>tractl numeric(6),<br>tractr numeric(6),
<br>blockl numeric(4),<br>blockr numeric(4),<br>CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>CONSTRAINT enforce_geotype_wkb_geometry CHECK<br>(geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS
<br>NULL),<br>CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>)<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br><br><br>------------------------------
<br><br>Message: 9<br>Date: Mon, 12 Feb 2007 11:32:55 -0000<br>From: "Pedro Doria Meunier" <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>><br>Subject: RE: [postgis-users] latitude / longitude from wkb_geometry
<br>To: "'PostGIS Users Discussion'"<br> <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: <001001c74e99$8de55bd0$a9b01370$@com
><br>Content-Type: text/plain; charset="us-ascii"<br><br>Hi Mark<br><br>Oooopsss. Did a mistake on the script there... :S<br><br>Where it reads<br>for ($point=0; $point<$npoints; $point++)<br><br>must be replaced with
<br>for ($point=1; $point<$npoints+1; $point++)<br><br>pointn() starts at 1 not 0... also the $npoints must go up to the exact nbr<br>of points returned by numpoints()<br><br>Sorry for that.<br><br>Also forgot to mention that the coordinates are returned in the form of
<br>dd.ddddd (pgsql spits them out as double precision)<br><br>Should you need them presented in another 'format' you need to do the<br>math...<br><br>HTH,<br>Pedro Doria Meunier<br><br>-----Original Message-----<br>
From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net
</a>] On Behalf Of Pedro<br>Doria Meunier<br>Sent: segunda-feira, 12 de Fevereiro de 2007 11:09<br>To: 'PostGIS Users Discussion'<br>Subject: RE: [postgis-users] latitude / longitude from wkb_geometry<br><br>Hi Mark
<br><br>Addendum to the previous post...<br>I didn't notice that the geometry was LINESTRING :]<br>So what we need here is to concatenate some functions:<br><br>SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf;
<br><br>This will give you the first coordinate of each line.<br><br>Should you want to process all coordinates of each linestring here's a<br>little PHP that might help:<br><br>// ------ cut here<br><?php<br>$connection = pg_connect("host=yourhost port=5432 dbname=yourdb
<br>user=yourusername password=yourpassword");<br>if (!$connection) {<br> print("Connection to the database failed.");<br> exit;<br>}<br>/*<br>Get all the records from the table.<br>We get the unique ogc_fid and the corresponding number of points for the
<br>linestring of this entry...<br>*/<br>$sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";<br>$myresult=pg_exec($connection, $sql);<br><br>for ($row=0; $row<pg_numrows($myresult); $row++) {<br> $unique=pg_result($myresult,$row,0);
<br> $npoints==pg_result($myresult,$row,0);<br> // now we process each point in this entry<br> for ($point=0; $point<$npoints; $point++) {<br> $sql= "SELECT x(pointn(wkb_geometry,$point)),
<br>y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";<br> $presult=pg_exec($connection, $sql);<br> $lon=pg_result($presult,0,0);<br> $lat=pg_result($presult,0,1);<br> /*
<br> Do whatever you wish with $lon, $lat....<br> */<br> }<br>}<br>?><br>// ------ cut here<br><br><br>If the gurus out there have a more efficient way to do this, I'd be more<br>than interested in hearing about it! ;-)
<br><br>HTH,<br>Pedro Doria Meunier.<br><br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>Sent: segunda-feira, 12 de Fevereiro de 2007 5:57<br>To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br>Subject: [postgis-users] latitude / longitude from wkb_geometry<br><br>how to get latitude and longitude from wkb_geometry?<br>My table structure is given below<br>thanks<br>mark<br><br>CREATE TABLE masuf<br>(<br>
ogc_fid serial NOT NULL,<br>wkb_geometry geometry,<br>module char(8),<br>tlid numeric(10),<br>side1 numeric(1),<br>source char(1),<br>fedirp char(2),<br>fename char(30),<br>fetype char(4),<br>fedirs char(2),<br>cfcc char(3),
<br>fraddl char(11),<br>toaddl char(11),<br>fraddr char(11),<br>toaddr char(11),<br>friaddl char(1),<br>toiaddl char(1),<br>friaddr char(1),<br>toiaddr char(1),<br>zipl numeric(5),<br>zipr numeric(5),<br>aianhhfpl numeric(5),
<br>aianhhfpr numeric(5),<br>aihhtlil char(1),<br>aihhtlir char(1),<br>census1 char(1),<br>census2 char(1),<br>statel numeric(2),<br>stater numeric(2),<br>countyl numeric(3),<br>countyr numeric(3),<br>cousubl numeric(5),<br>
cousubr numeric(5),<br>submcdl numeric(5),<br>submcdr numeric(5),<br>placel numeric(5),<br>placer numeric(5),<br>tractl numeric(6),<br>tractr numeric(6),<br>blockl numeric(4),<br>blockr numeric(4),<br>CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),
<br>CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>CONSTRAINT enforce_geotype_wkb_geometry CHECK<br>(geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>NULL),<br>CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)
<br>)<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br><br><br>------------------------------<br><br>Message: 10<br>Date: Mon, 12 Feb 2007 08:54:25 -0500
<br>From: "Obe, Regina" <<a href="mailto:robe.dnd@cityofboston.gov">robe.dnd@cityofboston.gov</a>><br>Subject: RE: [postgis-users] within_distance function<br>To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a>><br>Message-ID:<br> <<a href="mailto:53F9CF533E1AA14EA1F8C5C08ABC08D201815B15@ZDND.DND.boston.cob">53F9CF533E1AA14EA1F8C5C08ABC08D201815B15@ZDND.DND.boston.cob</a>>
<br>Content-Type: text/plain; charset="US-ASCII"<br><br>Great suggestion Paul! My SQL function is running just as fast as the<br>expand combination in my code.<br><br>My sql function looks like this<br>CREATE OR REPLACE FUNCTION within_distancesql(geom1 geometry, geom2
<br>geometry, dist double precision)<br> RETURNS boolean AS<br>$BODY$<br> SELECT (expand($1, $3) && $2 AND distance($1, $2) <= $3) ;<br>$BODY$<br> LANGUAGE 'sql' IMMUTABLE;<br><br>---<br>First I realized that I had my queries backward and I wasn't taking full
<br>advantage of my indexes because when I switched the order of my query<br>from<br><br>SELECT l.pid, l.the_geom<br>FROM landparcels l, landparcels l2<br>WHERE l2.pid = '1803570000' and (expand(l.the_geom, 1000) &&
l2.the_geom<br>AND distance(l.the_geom, l2.the_geom) <= 1000)<br><br>to<br><br>SELECT l.pid, l.the_geom<br>FROM landparcels l, landparcels l2<br>WHERE l2.pid = '1803570000' and (expand(l2.the_geom, 1000) &&
l.the_geom<br>AND distance(l.the_geom, l2.the_geom) <= 1000)<br><br>My time went from 609ms to 78ms. The original with_distance didn't<br>matter the order of the arguments since presumably it wasn't taking<br>
advantage of the gist index.<br><br>When I changed my query to<br>SELECT l.pid, l.the_geom<br>FROM landparcels l , landparcels l2<br>WHERE l2.pid = '1803570000' and within_distancesql(l2.the_geom,<br>l.the_geom, 1000)
<br><br>My time when to 78ms.<br><br>For the less optimized<br>SELECT l.pid, l.the_geom<br>FROM landparcels l , landparcels l2<br>WHERE l2.pid = '1803570000' and within_distancesql(l.the_geom,<br>l2.the_geom, 1000)
<br><br>time was 609ms<br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Paul<br>Ramsey<br>Sent: Friday, February 09, 2007 6:49 PM<br>To: PostGIS Users Discussion<br>Subject: Re: [postgis-users] within_distance function<br><br>Perhaps doing the function as a SQL function rather than a PL/PgSQL
<br>function. Shot in the dark.<br><br>P<br><br>Obe, Regina wrote:<br>> On further inspection, I realized my within_distance is not using the<br>> gist index and that the gain over the just distance check was because
<br>> of the efficiency of extent comparison instead of distance<br>> comparison.<br>><br>> I suppose there is no way to trick the optimizer into using an index<br>> with the function call. Something like a macro of some sort so it
<br>> macro replaces my shorter statement with the more efficient longer<br>> statement?<br>><br>> ________________________________<br>><br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a> on behalf of Obe,<br>> Regina Sent: Fri 2/9/2007 3:32 PM To: PostGIS Users Discussion<br>> Subject: [postgis-users] within_distance function<br>><br>><br>> I was hoping to simplify some of my distance queries by creating a
<br>> within_distance function which looks like<br>><br>> CREATE OR REPLACE FUNCTION within_distance(geom1 geometry, geom2<br>> geometry, dist double precision) RETURNS boolean AS $BODY$ BEGIN<br>> return (expand(geom1, dist) && geom2 AND distance(geom1, geom2) <=
<br>> dist) ; END;$BODY$ LANGUAGE 'plpgsql' IMMUTABLE;<br>><br>> Upon testing this I noticed the following observations<br>><br>> SELECT l.pid, l.the_geom FROM landparcels l , landparcels l2 WHERE<br>
> l2.pid = '1803570000' and within_distance(l.the_geom, l2.the_geom,<br>> 1000) ;<br>><br>> runs in 1610ms and returns 679 rows<br>><br>------------------------------------------------------------------------
<br>---<br>> SELECT l.pid, l.the_geom FROM landparcels l, landparcels l2 WHERE<br>> l2.pid = '1803570000' and (expand(l.the_geom, 1000) && l2.the_geom<br>> AND distance(l.the_geom, l2.the_geom) <= 1000)
<br>><br>> runs in 609 ms and returns 679 rows<br>><br>><br>------------------------------------------------------------------------<br>--<br>> SELECT l.pid, l.the_geom FROM landparcels l, landparcels l2 WHERE
<br>> l2.pid = '1803570000' and (distance(l.the_geom, l2.the_geom) <= 1000)<br>><br>><br>> runs in 5437 ms and returns 679 rows<br>><br>> ----- I ran the test a couple of times with similar results. So
<br>> apparently as far as I can tell, my function is probably using my<br>> gist indexes, but why is it 1/2 the speed of the regular expand call?<br>><br>><br>><br>><br>> ________________________________
<br>><br>><br>><br>> The substance of this message, including any attachments, may be<br>> confidential, legally privileged and/or exempt from disclosure<br>> pursuant to Massachusetts law. It is intended solely for the
<br>> addressee. If you received this in error, please contact the sender<br>> and delete the material from any computer.<br>><br>><br>><br>><br>><br>------------------------------------------------------------------------
<br>><br>><br>> _______________________________________________ postgis-users mailing<br>> list <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br>--<br><br> Paul Ramsey<br> Refractions Research<br> <a href="http://www.refractions.net">http://www.refractions.net</a><br> <a href="mailto:pramsey@refractions.net">
pramsey@refractions.net</a><br> Phone: 250-383-3022<br> Cell: 250-885-0632<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br>------------------------------<br><br>Message: 11<br>Date: Mon, 12 Feb 2007 09:29:58 -0500
<br>From: "Obe, Regina" <<a href="mailto:robe.dnd@cityofboston.gov">robe.dnd@cityofboston.gov</a>><br>Subject: RE: [postgis-users] distance from imported tiger database<br>To: "PostGIS Users Discussion" <
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID:<br> <<a href="mailto:53F9CF533E1AA14EA1F8C5C08ABC08D201815BAC@ZDND.DND.boston.cob">53F9CF533E1AA14EA1F8C5C08ABC08D201815BAC@ZDND.DND.boston.cob
</a>><br>Content-Type: text/plain; charset="us-ascii"<br><br>Hi Mark,<br><br>What does your location data look like. Are you for example trying to<br>compare distance of point locations from a street?<br><br>
For example the below will give you a listing of all streets that are<br>within 100 meters of the POINT(-71.10668 42.27081) - note point is in<br>long, lat.<br><br>The SRID 26986 you'll need to change to the SRID most suitable for your
<br>particular location which you can look up in the spatial_ref_sys .<br>Basically you want the measurement to be units you want to measure by<br>and to be suitable for the region you are looking at. For this example,<br>
I transformed my geometries to NAD 83 Mass State Plane meters (26986).<br>Items you will want to replace are in bold. In general I find it useful<br>to store the geometry field in the coordinate system you will be using<br>
mostly to minimize on having to write messy transform calls and to<br>improve on speed.<br><br>SELECT fename, fetype, fedirps<br>FROM masuf<br>WHERE expand(transform(wkb_geometry, 26986), 100) &&<br>transform(geometryfromtext('POINT(-
71.10668 42.27081)', 4269), 26986)<br> AND distance(transform(wkb_geometry, 26986),<br>transform(geometryfromtext('POINT(-71.10668 42.27081)', 4269), 26986))<br><= 100<br><br>Hope that helps,<br>Regina<br>
<br><br><br><br>________________________________<br><br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>Sent: Sunday, February 11, 2007 9:03 PM<br>To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>Subject: [postgis-users] distance from imported tiger database
<br><br><br>Hi<br>I imported the tiger database with the ogr tool. It has created table<br>with the structure below.<br>geometry_columns has an entry like this:<br><br>197276;"''";"public";"masuf";"wkb_geometry";"2";"4269";"LINESTRING"
<br><br>Can you tell how I can query for locations based on distance using<br>these?<br>thanks a lot<br>mark<br><br>CREATE TABLE masuf<br>(<br>ogc_fid serial NOT NULL,<br>wkb_geometry geometry,<br>module char(8),<br>tlid numeric(10),
<br>side1 numeric(1),<br>source char(1),<br>fedirp char(2),<br>fename char(30),<br>fetype char(4),<br>fedirs char(2),<br>cfcc char(3),<br>fraddl char(11),<br>toaddl char(11),<br>fraddr char(11),<br>toaddr char(11),<br>friaddl char(1),
<br>toiaddl char(1),<br>friaddr char(1),<br>toiaddr char(1),<br>zipl numeric(5),<br>zipr numeric(5),<br>aianhhfpl numeric(5),<br>aianhhfpr numeric(5),<br>aihhtlil char(1),<br>aihhtlir char(1),<br>census1 char(1),<br>census2 char(1),
<br>statel numeric(2),<br>stater numeric(2),<br>countyl numeric(3),<br>countyr numeric(3),<br>cousubl numeric(5),<br>cousubr numeric(5),<br>submcdl numeric(5),<br>submcdr numeric(5),<br>placel numeric(5),<br>placer numeric(5),
<br>tractl numeric(6),<br>tractr numeric(6),<br>blockl numeric(4),<br>blockr numeric(4),<br>CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>CONSTRAINT enforce_geotype_wkb_geometry CHECK
<br>(geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>NULL),<br>CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>)<br><br><br><br>On 2/11/07, mark <<a href="mailto:rkmr.em@gmail.com">
rkmr.em@gmail.com</a>> wrote:<br><br> hi<br> are there any tools for importing the tiger database into<br>postgres to use with postgis?<br> thanks !<br> mark<br><br><br><br><br><br><br>-----------------------------------------
<br>The substance of this message, including any attachments, may be<br>confidential, legally privileged and/or exempt from disclosure<br>pursuant to Massachusetts law. It is intended<br>solely for the addressee. If you received this in error, please
<br>contact the sender and delete the material from any computer.<br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <a href="http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/0c2f8295/attachment-0001.html">
http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/0c2f8295/attachment-0001.html</a><br><br>------------------------------<br><br>Message: 12<br>Date: Mon, 12 Feb 2007 09:45:37 -0500<br>From: "Obe, Regina" <
<a href="mailto:robe.dnd@cityofboston.gov">robe.dnd@cityofboston.gov</a>><br>Subject: RE: [postgis-users] distance from imported tiger database<br>To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a>><br>Message-ID:<br> <<a href="mailto:53F9CF533E1AA14EA1F8C5C08ABC08D201815BF6@ZDND.DND.boston.cob">53F9CF533E1AA14EA1F8C5C08ABC08D201815BF6@ZDND.DND.boston.cob</a>>
<br>Content-Type: text/plain; charset="us-ascii"<br><br>Slight correction. I think its more efficient to write it like this so<br>that expand is only called once.<br><br>SELECT fename, fetype, fedirps<br>FROM masuf
<br>WHERE expand(transform(geometryfromtext('POINT(-71.10668 42.27081)',<br>4269), 26986),100) && transform(wkb_geometry, 26986) AND<br>distance(transform(wkb_geometry, 26986),<br>transform(geometryfromtext('POINT(-
71.10668 42.27081)', 4269), 26986))<br><= 100<br><br><br>________________________________<br><br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
<br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Obe,<br>Regina<br>Sent: Monday, February 12, 2007 9:30 AM<br>To: PostGIS Users Discussion
<br>Subject: RE: [postgis-users] distance from imported tiger database<br><br><br>Hi Mark,<br><br>What does your location data look like. Are you for example trying to<br>compare distance of point locations from a street?
<br><br>For example the below will give you a listing of all streets that are<br>within 100 meters of the POINT(-71.10668 42.27081) - note point is in<br>long, lat.<br><br>The SRID 26986 you'll need to change to the SRID most suitable for your
<br>particular location which you can look up in the spatial_ref_sys .<br>Basically you want the measurement to be units you want to measure by<br>and to be suitable for the region you are looking at. For this example,<br>
I transformed my geometries to NAD 83 Mass State Plane meters (26986).<br>Items you will want to replace are in bold. In general I find it useful<br>to store the geometry field in the coordinate system you will be using<br>
mostly to minimize on having to write messy transform calls and to<br>improve on speed.<br><br>SELECT fename, fetype, fedirps<br>FROM masuf<br>WHERE expand(transform(wkb_geometry, 26986), 100) &&<br>transform(geometryfromtext('POINT(-
71.10668 42.27081)', 4269), 26986)<br> AND distance(transform(wkb_geometry, 26986),<br>transform(geometryfromtext('POINT(-71.10668 42.27081)', 4269), 26986))<br><= 100<br><br>Hope that helps,<br>Regina<br>
<br><br><br><br>________________________________<br><br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>Sent: Sunday, February 11, 2007 9:03 PM<br>To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>Subject: [postgis-users] distance from imported tiger database
<br><br><br>Hi<br>I imported the tiger database with the ogr tool. It has created table<br>with the structure below.<br>geometry_columns has an entry like this:<br><br>197276;"''";"public";"masuf";"wkb_geometry";"2";"4269";"LINESTRING"
<br><br>Can you tell how I can query for locations based on distance using<br>these?<br>thanks a lot<br>mark<br><br>CREATE TABLE masuf<br>(<br>ogc_fid serial NOT NULL,<br>wkb_geometry geometry,<br>module char(8),<br>tlid numeric(10),
<br>side1 numeric(1),<br>source char(1),<br>fedirp char(2),<br>fename char(30),<br>fetype char(4),<br>fedirs char(2),<br>cfcc char(3),<br>fraddl char(11),<br>toaddl char(11),<br>fraddr char(11),<br>toaddr char(11),<br>friaddl char(1),
<br>toiaddl char(1),<br>friaddr char(1),<br>toiaddr char(1),<br>zipl numeric(5),<br>zipr numeric(5),<br>aianhhfpl numeric(5),<br>aianhhfpr numeric(5),<br>aihhtlil char(1),<br>aihhtlir char(1),<br>census1 char(1),<br>census2 char(1),
<br>statel numeric(2),<br>stater numeric(2),<br>countyl numeric(3),<br>countyr numeric(3),<br>cousubl numeric(5),<br>cousubr numeric(5),<br>submcdl numeric(5),<br>submcdr numeric(5),<br>placel numeric(5),<br>placer numeric(5),
<br>tractl numeric(6),<br>tractr numeric(6),<br>blockl numeric(4),<br>blockr numeric(4),<br>CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>CONSTRAINT enforce_geotype_wkb_geometry CHECK
<br>(geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>NULL),<br>CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>)<br><br><br><br>On 2/11/07, mark <<a href="mailto:rkmr.em@gmail.com">
rkmr.em@gmail.com</a>> wrote:<br><br> hi<br> are there any tools for importing the tiger database into<br>postgres to use with postgis?<br> thanks !<br> mark<br><br><br><br>________________________________
<br><br><br><br>The substance of this message, including any attachments, may be<br>confidential, legally privileged and/or exempt from disclosure<br>pursuant to Massachusetts law. It is intended solely for the<br>addressee. If you received this in error, please contact the sender
<br>and delete the material from any computer.<br><br><br><br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <a href="http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/b96a3dec/attachment-0001.html">
http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/b96a3dec/attachment-0001.html</a><br><br>------------------------------<br><br>Message: 13<br>Date: Mon, 12 Feb 2007 07:31:20 -0800<br>From: Paul Ramsey <
<a href="mailto:pramsey@refractions.net">pramsey@refractions.net</a>><br>Subject: Re: [postgis-users] within_distance function<br>To: PostGIS Users Discussion <<a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a>><br>Message-ID: <<a href="mailto:51BB5AF0-023A-4BDF-9A6A-D59923226FDA@refractions.net">51BB5AF0-023A-4BDF-9A6A-D59923226FDA@refractions.net</a>><br>Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
<br><br>Interesting. I guess the optimizer has an easier time bringing the<br>SQL function into the plan.<br><br>Now, to be "standards compliant" you should rename your function<br>"DWithin()". Should we include such a thing in the main distro? Seems
<br>a pretty common construction in spatial SQL.<br><br>I have often wondered about the wisdom of wrapping the spatial<br>booleans in appropriate SQL to auto-generate the necessary index<br>calls. On the one hand it makes life easier and more automagic for
<br>new users. On the other hand, explicitness of intent is lost for<br>others.<br><br>P<br><br><br>On 12-Feb-07, at 5:54 AM, Obe, Regina wrote:<br><br>> Great suggestion Paul! My SQL function is running just as fast as the
<br>> expand combination in my code.<br>><br>> My sql function looks like this<br>> CREATE OR REPLACE FUNCTION within_distancesql(geom1 geometry, geom2<br>> geometry, dist double precision)<br>> RETURNS boolean AS
<br>> $BODY$<br>> SELECT (expand($1, $3) && $2 AND distance($1, $2) <= $3) ;<br>> $BODY$<br>> LANGUAGE 'sql' IMMUTABLE;<br>><br>> ---<br>> First I realized that I had my queries backward and I wasn't taking
<br>> full<br>> advantage of my indexes because when I switched the order of my query<br>> from<br>><br>> SELECT l.pid, l.the_geom<br>> FROM landparcels l, landparcels l2<br>> WHERE l2.pid = '1803570000' and (expand(
l.the_geom, 1000) &&<br>> l2.the_geom<br>> AND distance(l.the_geom, l2.the_geom) <= 1000)<br>><br>> to<br>><br>> SELECT l.pid, l.the_geom<br>> FROM landparcels l, landparcels l2<br>> WHERE
l2.pid = '1803570000' and (expand(l2.the_geom, 1000) &&<br>> l.the_geom<br>> AND distance(l.the_geom, l2.the_geom) <= 1000)<br>><br>> My time went from 609ms to 78ms. The original with_distance didn't
<br>> matter the order of the arguments since presumably it wasn't taking<br>> advantage of the gist index.<br>><br>> When I changed my query to<br>> SELECT l.pid, l.the_geom<br>> FROM landparcels l , landparcels l2
<br>> WHERE l2.pid = '1803570000' and within_distancesql(l2.the_geom,<br>> l.the_geom, 1000)<br>><br>> My time when to 78ms.<br>><br>> For the less optimized<br>> SELECT l.pid, l.the_geom<br>> FROM landparcels l , landparcels l2
<br>> WHERE l2.pid = '1803570000' and within_distancesql(l.the_geom,<br>> l2.the_geom, 1000)<br>><br>> time was 609ms<br>><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of<br>> Paul<br>> Ramsey<br>
> Sent: Friday, February 09, 2007 6:49 PM<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] within_distance function<br>><br>> Perhaps doing the function as a SQL function rather than a PL/PgSQL
<br>> function. Shot in the dark.<br>><br>> P<br>><br>> Obe, Regina wrote:<br>>> On further inspection, I realized my within_distance is not using the<br>>> gist index and that the gain over the just distance check was because
<br>>> of the efficiency of extent comparison instead of distance<br>>> comparison.<br>>><br>>> I suppose there is no way to trick the optimizer into using an index<br>>> with the function call. Something like a macro of some sort so it
<br>>> macro replaces my shorter statement with the more efficient longer<br>>> statement?<br>>><br>>> ________________________________<br>>><br>>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a> on behalf of Obe,<br>>> Regina Sent: Fri 2/9/2007 3:32 PM To: PostGIS Users Discussion<br>>> Subject: [postgis-users] within_distance function<br>>><br>>>
<br>>> I was hoping to simplify some of my distance queries by creating a<br>>> within_distance function which looks like<br>>><br>>> CREATE OR REPLACE FUNCTION within_distance(geom1 geometry, geom2
<br>>> geometry, dist double precision) RETURNS boolean AS $BODY$ BEGIN<br>>> return (expand(geom1, dist) && geom2 AND distance(geom1, geom2) <=<br>>> dist) ; END;$BODY$ LANGUAGE 'plpgsql' IMMUTABLE;
<br>>><br>>> Upon testing this I noticed the following observations<br>>><br>>> SELECT l.pid, l.the_geom FROM landparcels l , landparcels l2 WHERE<br>>> l2.pid = '1803570000' and within_distance(
l.the_geom, l2.the_geom,<br>>> 1000) ;<br>>><br>>> runs in 1610ms and returns 679 rows<br>>><br>> ----------------------------------------------------------------------<br>> --<br>> ---<br>
>> SELECT l.pid, l.the_geom FROM landparcels l, landparcels l2 WHERE<br>>> l2.pid = '1803570000' and (expand(l.the_geom, 1000) && l2.the_geom<br>>> AND distance(l.the_geom, l2.the_geom) <= 1000)
<br>>><br>>> runs in 609 ms and returns 679 rows<br>>><br>>><br>> ----------------------------------------------------------------------<br>> --<br>> --<br>>> SELECT l.pid, l.the_geom
FROM landparcels l, landparcels l2 WHERE<br>>> l2.pid = '1803570000' and (distance(l.the_geom, l2.the_geom) <= 1000)<br>>><br>>><br>>> runs in 5437 ms and returns 679 rows<br>>><br>
>> ----- I ran the test a couple of times with similar results. So<br>>> apparently as far as I can tell, my function is probably using my<br>>> gist indexes, but why is it 1/2 the speed of the regular expand call?
<br>>><br>>><br>>><br>>><br>>> ________________________________<br>>><br>>><br>>><br>>> The substance of this message, including any attachments, may be<br>>> confidential, legally privileged and/or exempt from disclosure
<br>>> pursuant to Massachusetts law. It is intended solely for the<br>>> addressee. If you received this in error, please contact the sender<br>>> and delete the material from any computer.<br>>><br>
>><br>>><br>>><br>>><br>> ----------------------------------------------------------------------<br>> --<br>>><br>>><br>>> _______________________________________________ postgis-users mailing
<br>>> list <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br>><br>><br>> --<br>><br>> Paul Ramsey<br>> Refractions Research<br>> <a href="http://www.refractions.net">http://www.refractions.net</a><br>> <a href="mailto:pramsey@refractions.net">
pramsey@refractions.net</a><br>> Phone: 250-383-3022<br>> Cell: 250-885-0632<br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br><br>------------------------------<br><br>Message: 14<br>Date: Mon, 12 Feb 2007 16:49:16 +0100<br>From: Just van den Broecke <<a href="mailto:just@justobjects.nl">
just@justobjects.nl</a>><br>Subject: [postgis-users] appending to a LINESTRING<br>To: PostGIS Users Discussion <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>
Message-ID: <<a href="mailto:45D08C7C.6000600@justobjects.nl">45D08C7C.6000600@justobjects.nl</a>><br>Content-Type: text/plain; charset=us-ascii; format=flowed<br><br>Hi,<br><br>What is the way to go for appending POINTs to a LINESTRING without
<br>reading the entire LINESTRING into the client ? i.e. for example<br><br>SELECT AddPoint(line, GeomFromText('POINT(4.92 52.35)',4326)) as line<br>FROM ( SELECT line FROM g_track WHERE id=101) foo;<br><br>Will do the append but will still require an UPDATE to be done with the
<br>resulting line. Would "one-shot" appending require a (plpg)sql function<br>or is there some other way ?<br><br>thanks,<br><br>--Just<br><br>Just van den Broecke<br><a href="http://www.justobjects.nl">http://www.justobjects.nl
</a><br>The Netherlands<br><br><br><br><br><br><br><br>------------------------------<br><br>Message: 15<br>Date: Mon, 12 Feb 2007 10:57:23 -0500<br>From: "Obe, Regina" <<a href="mailto:robe.dnd@cityofboston.gov">
robe.dnd@cityofboston.gov</a>><br>Subject: RE: [postgis-users] within_distance function<br>To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a>><br>Message-ID:<br> <<a href="mailto:53F9CF533E1AA14EA1F8C5C08ABC08D201815D25@ZDND.DND.boston.cob">53F9CF533E1AA14EA1F8C5C08ABC08D201815D25@ZDND.DND.boston.cob</a>><br>Content-Type: text/plain; charset="US-ASCII"
<br><br>I think it would make life a lot easier if we did have it in the main<br>distro. People always seem to forget the expand or do it the wrong way<br>as I have done ... and its hard to explain it to some people why that is
<br>necessary and then it gets a little irritating to write it out if you<br>are doing it a lot.<br><br>Getting back to the explicitness - I guess the main thing that bothered<br>me initially about my solution I had proposed is the fact that the order
<br>of the geometries matters in some cases. Its more obvious to see when<br>you are looking at the full expand && .. why the order matters, but you<br>lose that with the blackboxing of the function. Then I realized if I
<br>expand both ways, the planner is smart enough to figure out which expand<br>is more efficient to do. So my revised function looks like this<br><br>CREATE OR REPLACE FUNCTION DWithin(geom1 geometry, geom2 geometry, dist
<br>double precision)<br> RETURNS boolean AS<br>$BODY$<br> SELECT ((expand($1, $3) && $2) AND (expand($2, $3) && $1) AND<br>distance($1, $2) <= $3) ;<br>$BODY$<br> LANGUAGE 'sql' IMMUTABLE;
<br><br>--Results are as follows<br>SELECT l.pid, l.the_geom<br>FROM landparcels l , landparcels l2<br>WHERE l2.pid = '1803570000' and DWithin(l2.the_geom, l.the_geom, 1000);<br><br>(finished in 79ms and returns 679 rows)
<br><br>SELECT l.pid, l.the_geom<br>FROM landparcels l , landparcels l2<br>WHERE l2.pid = '1803570000' and DWithin(l.the_geom, l2.the_geom, 1000) ;<br>(finishes between 69ms, 109ms, 78 ms and returns 679 rows)<br>
<br>Thanks,<br>Regina<br><br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Paul<br>Ramsey<br>Sent: Monday, February 12, 2007 10:31 AM<br>To: PostGIS Users Discussion<br>Subject: Re: [postgis-users] within_distance function<br><br>Interesting. I guess the optimizer has an easier time bringing the
<br>SQL function into the plan.<br><br>Now, to be "standards compliant" you should rename your function<br>"DWithin()". Should we include such a thing in the main distro? Seems<br>a pretty common construction in spatial SQL.
<br><br>I have often wondered about the wisdom of wrapping the spatial<br>booleans in appropriate SQL to auto-generate the necessary index<br>calls. On the one hand it makes life easier and more automagic for<br>new users. On the other hand, explicitness of intent is lost for
<br>others.<br><br>P<br><br><br>On 12-Feb-07, at 5:54 AM, Obe, Regina wrote:<br><br>> Great suggestion Paul! My SQL function is running just as fast as the<br>> expand combination in my code.<br>><br>> My sql function looks like this
<br>> CREATE OR REPLACE FUNCTION within_distancesql(geom1 geometry, geom2<br>> geometry, dist double precision)<br>> RETURNS boolean AS<br>> $BODY$<br>> SELECT (expand($1, $3) && $2 AND distance($1, $2) <= $3) ;
<br>> $BODY$<br>> LANGUAGE 'sql' IMMUTABLE;<br>><br>> ---<br>> First I realized that I had my queries backward and I wasn't taking<br>> full<br>> advantage of my indexes because when I switched the order of my query
<br>> from<br>><br>> SELECT l.pid, l.the_geom<br>> FROM landparcels l, landparcels l2<br>> WHERE l2.pid = '1803570000' and (expand(l.the_geom, 1000) &&<br>> l2.the_geom<br>> AND distance(
l.the_geom, l2.the_geom) <= 1000)<br>><br>> to<br>><br>> SELECT l.pid, l.the_geom<br>> FROM landparcels l, landparcels l2<br>> WHERE l2.pid = '1803570000' and (expand(l2.the_geom, 1000) &&
<br>> l.the_geom<br>> AND distance(l.the_geom, l2.the_geom) <= 1000)<br>><br>> My time went from 609ms to 78ms. The original with_distance didn't<br>> matter the order of the arguments since presumably it wasn't taking
<br>> advantage of the gist index.<br>><br>> When I changed my query to<br>> SELECT l.pid, l.the_geom<br>> FROM landparcels l , landparcels l2<br>> WHERE l2.pid = '1803570000' and within_distancesql(
l2.the_geom,<br>> l.the_geom, 1000)<br>><br>> My time when to 78ms.<br>><br>> For the less optimized<br>> SELECT l.pid, l.the_geom<br>> FROM landparcels l , landparcels l2<br>> WHERE l2.pid = '1803570000' and within_distancesql(
l.the_geom,<br>> l2.the_geom, 1000)<br>><br>> time was 609ms<br>><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net
</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of<br>> Paul<br>> Ramsey<br>> Sent: Friday, February 09, 2007 6:49 PM
<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] within_distance function<br>><br>> Perhaps doing the function as a SQL function rather than a PL/PgSQL<br>> function. Shot in the dark.<br>
><br>> P<br>><br>> Obe, Regina wrote:<br>>> On further inspection, I realized my within_distance is not using the<br>>> gist index and that the gain over the just distance check was because<br>>> of the efficiency of extent comparison instead of distance
<br>>> comparison.<br>>><br>>> I suppose there is no way to trick the optimizer into using an index<br>>> with the function call. Something like a macro of some sort so it<br>>> macro replaces my shorter statement with the more efficient longer
<br>>> statement?<br>>><br>>> ________________________________<br>>><br>>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
on behalf of Obe,<br>>> Regina Sent: Fri 2/9/2007 3:32 PM To: PostGIS Users Discussion<br>>> Subject: [postgis-users] within_distance function<br>>><br>>><br>>> I was hoping to simplify some of my distance queries by creating a
<br>>> within_distance function which looks like<br>>><br>>> CREATE OR REPLACE FUNCTION within_distance(geom1 geometry, geom2<br>>> geometry, dist double precision) RETURNS boolean AS $BODY$ BEGIN<br>
>> return (expand(geom1, dist) && geom2 AND distance(geom1, geom2) <=<br>>> dist) ; END;$BODY$ LANGUAGE 'plpgsql' IMMUTABLE;<br>>><br>>> Upon testing this I noticed the following observations
<br>>><br>>> SELECT l.pid, l.the_geom FROM landparcels l , landparcels l2 WHERE<br>>> l2.pid = '1803570000' and within_distance(l.the_geom, l2.the_geom,<br>>> 1000) ;<br>>><br>>> runs in 1610ms and returns 679 rows
<br>>><br>> ----------------------------------------------------------------------<br><br>> --<br>> ---<br>>> SELECT l.pid, l.the_geom FROM landparcels l, landparcels l2 WHERE<br>>> l2.pid = '1803570000' and (expand(
l.the_geom, 1000) && l2.the_geom<br>>> AND distance(l.the_geom, l2.the_geom) <= 1000)<br>>><br>>> runs in 609 ms and returns 679 rows<br>>><br>>><br>> ----------------------------------------------------------------------
<br><br>> --<br>> --<br>>> SELECT l.pid, l.the_geom FROM landparcels l, landparcels l2 WHERE<br>>> l2.pid = '1803570000' and (distance(l.the_geom, l2.the_geom) <= 1000)<br>>><br>>>
<br>>> runs in 5437 ms and returns 679 rows<br>>><br>>> ----- I ran the test a couple of times with similar results. So<br>>> apparently as far as I can tell, my function is probably using my<br>>> gist indexes, but why is it 1/2 the speed of the regular expand call?
<br>>><br>>><br>>><br>>><br>>> ________________________________<br>>><br>>><br>>><br>>> The substance of this message, including any attachments, may be<br>>> confidential, legally privileged and/or exempt from disclosure
<br>>> pursuant to Massachusetts law. It is intended solely for the<br>>> addressee. If you received this in error, please contact the sender<br>>> and delete the material from any computer.<br>>><br>
>><br>>><br>>><br>>><br>> ----------------------------------------------------------------------<br><br>> --<br>>><br>>><br>>> _______________________________________________ postgis-users mailing
<br>>> list <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br>><br>><br>> --<br>><br>> Paul Ramsey<br>> Refractions Research<br>> <a href="http://www.refractions.net">http://www.refractions.net</a><br>> <a href="mailto:pramsey@refractions.net">
pramsey@refractions.net</a><br>> Phone: 250-383-3022<br>> Cell: 250-885-0632<br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br>------------------------------<br><br>Message: 16<br>Date: Mon, 12 Feb 2007 08:42:16 -0800
<br>From: mark <<a href="mailto:rkmr.em@gmail.com">rkmr.em@gmail.com</a>><br>Subject: Re: [postgis-users] latitude / longitude from wkb_geometry<br>To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a>><br>Message-ID:<br> <<a href="mailto:e04d2e90702120842t1dff5ae4ua9c65be49bfe76fd@mail.gmail.com">e04d2e90702120842t1dff5ae4ua9c65be49bfe76fd@mail.gmail.com</a>><br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br><br>Hi Pedro<br>Thanks for your replies.<br>I tried your SQL query on this row:<br><br>SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf<br>
where ogc_fid=62560;<br>"62560";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C63247B7A5EC07DAEB6627FA54240";"TGR06085";"123181609";"";"O";"";"";"";"";"F10";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"6";"6";"85";"85";"92830";"92830";"";"";"68000";"68000";"502601";"502601";"1019";"1019"
<br><br>I just get Null values for x and y.<br><br>Can you tell what is wrong?<br>thanks a lot!<br>mark<br><br><br>On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:
<br>> Hi Mark<br>><br>> Addendum to the previous post...<br>> I didn't notice that the geometry was LINESTRING :]<br>> So what we need here is to concatenate some functions:<br>><br>> SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf;
<br>><br>> This will give you the first coordinate of each line.<br>><br>> Should you want to process all coordinates of each linestring here's a<br>> little PHP that might help:<br>><br>> // ------ cut here
<br>> <?php<br>> $connection = pg_connect("host=yourhost port=5432 dbname=yourdb<br>> user=yourusername password=yourpassword");<br>> if (!$connection) {<br>> print("Connection to the database failed.");
<br>> exit;<br>> }<br>> /*<br>> Get all the records from the table.<br>> We get the unique ogc_fid and the corresponding number of points for the<br>> linestring of this entry...<br>> */<br>> $sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";
<br>> $myresult=pg_exec($connection, $sql);<br>><br>> for ($row=0; $row<pg_numrows($myresult); $row++) {<br>> $unique=pg_result($myresult,$row,0);<br>> $npoints==pg_result($myresult,$row,0);
<br>> // now we process each point in this entry<br>> for ($point=0; $point<$npoints; $point++) {<br>> $sql= "SELECT x(pointn(wkb_geometry,$point)),<br>> y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";
<br>> $presult=pg_exec($connection, $sql);<br>> $lon=pg_result($presult,0,0);<br>> $lat=pg_result($presult,0,1);<br>> /*<br>> Do whatever you wish with $lon, $lat....
<br>> */<br>> }<br>> }<br>> ?><br>> // ------ cut here<br>><br>><br>> If the gurus out there have a more efficient way to do this, I'd be more<br>> than interested in hearing about it! ;-)
<br>><br>> HTH,<br>> Pedro Doria Meunier.<br>><br>><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net
</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> Sent: segunda-feira, 12 de Fevereiro de 2007 5:57<br>> To:
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> Subject: [postgis-users] latitude / longitude from wkb_geometry<br>><br>> how to get latitude and longitude from wkb_geometry?
<br>> My table structure is given below<br>> thanks<br>> mark<br>><br>> CREATE TABLE masuf<br>> (<br>> ogc_fid serial NOT NULL,<br>> wkb_geometry geometry,<br>> module char(8),<br>> tlid numeric(10),
<br>> side1 numeric(1),<br>> source char(1),<br>> fedirp char(2),<br>> fename char(30),<br>> fetype char(4),<br>> fedirs char(2),<br>> cfcc char(3),<br>> fraddl char(11),<br>> toaddl char(11),<br>
> fraddr char(11),<br>> toaddr char(11),<br>> friaddl char(1),<br>> toiaddl char(1),<br>> friaddr char(1),<br>> toiaddr char(1),<br>> zipl numeric(5),<br>> zipr numeric(5),<br>> aianhhfpl numeric(5),
<br>> aianhhfpr numeric(5),<br>> aihhtlil char(1),<br>> aihhtlir char(1),<br>> census1 char(1),<br>> census2 char(1),<br>> statel numeric(2),<br>> stater numeric(2),<br>> countyl numeric(3),<br>> countyr numeric(3),
<br>> cousubl numeric(5),<br>> cousubr numeric(5),<br>> submcdl numeric(5),<br>> submcdr numeric(5),<br>> placel numeric(5),<br>> placer numeric(5),<br>> tractl numeric(6),<br>> tractr numeric(6),<br>
> blockl numeric(4),<br>> blockr numeric(4),<br>> CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>> CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>> CONSTRAINT enforce_geotype_wkb_geometry CHECK
<br>> (geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>> NULL),<br>> CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>> )<br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br><br><br>------------------------------
<br><br>Message: 17<br>Date: Mon, 12 Feb 2007 08:46:46 -0800<br>From: mark <<a href="mailto:rkmr.em@gmail.com">rkmr.em@gmail.com</a>><br>Subject: Re: [postgis-users] distance from imported tiger database<br>To: "PostGIS Users Discussion" <
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID:<br> <<a href="mailto:e04d2e90702120846p2ec8b977r5081345e7dc6a259@mail.gmail.com">e04d2e90702120846p2ec8b977r5081345e7dc6a259@mail.gmail.com
</a>><br>Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br><br>Hi Regina<br>Thanks a lot for your reply.<br><br>My location data is a US street address. Can you tell me what is the<br>best way to get to a POINT from the US Street address? I am
<br>considering searching the masuf table that I have which I have<br>populated with TIGER database (which is so great!!!!!!). Even if I map<br>the US street address to a row I still end up with a line geometry<br>right?<br>
Correct me if I am wrong! Am a complete newbie!<br>thanks a lot again!<br>mark<br><br>On 2/12/07, Obe, Regina <<a href="mailto:robe.dnd@cityofboston.gov">robe.dnd@cityofboston.gov</a>> wrote:<br>><br>><br>> Slight correction. I think its more efficient to write it like this so that
<br>> expand is only called once.<br>><br>><br>> SELECT fename, fetype, fedirps<br>> FROM masuf<br>> WHERE expand(transform(geometryfromtext('POINT(-71.10668<br>> 42.27081)', 4269), 26986),100) && transform(wkb_geometry, 26986) AND
<br>> distance(transform(wkb_geometry, 26986),<br>> transform(geometryfromtext('POINT(-71.10668 42.27081)', 4269), 26986)) <=<br>> 100<br>><br>> ________________________________<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On<br>> Behalf Of Obe, Regina<br>> Sent: Monday, February 12, 2007 9:30 AM
<br>> To: PostGIS Users Discussion<br>> Subject: RE: [postgis-users] distance from imported tiger database<br>><br>><br>><br>> Hi Mark,<br>><br>> What does your location data look like. Are you for example trying to
<br>> compare distance of point locations from a street?<br>><br>> For example the below will give you a listing of all streets that are within<br>> 100 meters of the POINT(-71.10668 42.27081) - note point is in long, lat.
<br>><br>> The SRID 26986 you'll need to change to the SRID most suitable for your<br>> particular location which you can look up in the spatial_ref_sys .<br>> Basically you want the measurement to be units you want to measure by and to
<br>> be suitable for the region you are looking at. For this example, I<br>> transformed my geometries to NAD 83 Mass State Plane meters (26986). Items<br>> you will want to replace are in bold. In general I find it useful to store
<br>> the geometry field in the coordinate system you will be using mostly to<br>> minimize on having to write messy transform calls and to improve on speed.<br>><br>> SELECT fename, fetype, fedirps<br>> FROM masuf
<br>> WHERE expand(transform(wkb_geometry, 26986), 100) &&<br>> transform(geometryfromtext('POINT(-71.10668 42.27081)', 4269), 26986)<br>> AND distance(transform(wkb_geometry, 26986),<br>> transform(geometryfromtext('POINT(-
71.10668 42.27081)', 4269), 26986)) <=<br>> 100<br>><br>> Hope that helps,<br>> Regina<br>><br>><br>><br>> ________________________________<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On<br>> Behalf Of mark<br>> Sent: Sunday, February 11, 2007 9:03 PM
<br>> To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> Subject: [postgis-users] distance from imported tiger database<br>><br>><br>> Hi<br>> I imported the tiger database with the ogr tool. It has created table with
<br>> the structure below.<br>> geometry_columns has an entry like this:<br>><br>> 197276;"''";"public";"masuf";"wkb_geometry";"2";"4269";"LINESTRING"
<br>><br>> Can you tell how I can query for locations based on distance using these?<br>> thanks a lot<br>> mark<br>><br>> CREATE TABLE masuf<br>> (<br>> ogc_fid serial NOT NULL,<br>> wkb_geometry geometry,
<br>> module char(8),<br>> tlid numeric(10),<br>> side1 numeric(1),<br>> source char(1),<br>> fedirp char(2),<br>> fename char(30),<br>> fetype char(4),<br>> fedirs char(2),<br>> cfcc char(3),<br>
> fraddl char(11),<br>> toaddl char(11),<br>> fraddr char(11),<br>> toaddr char(11),<br>> friaddl char(1),<br>> toiaddl char(1),<br>> friaddr char(1),<br>> toiaddr char(1),<br>> zipl numeric(5),
<br>> zipr numeric(5),<br>> aianhhfpl numeric(5),<br>> aianhhfpr numeric(5),<br>> aihhtlil char(1),<br>> aihhtlir char(1),<br>> census1 char(1),<br>> census2 char(1),<br>> statel numeric(2),<br>> stater numeric(2),
<br>> countyl numeric(3),<br>> countyr numeric(3),<br>> cousubl numeric(5),<br>> cousubr numeric(5),<br>> submcdl numeric(5),<br>> submcdr numeric(5),<br>> placel numeric(5),<br>> placer numeric(5),
<br>> tractl numeric(6),<br>> tractr numeric(6),<br>> blockl numeric(4),<br>> blockr numeric(4),<br>> CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>> CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),
<br>> CONSTRAINT enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) =<br>> 'LINESTRING'::text OR wkb_geometry IS NULL),<br>> CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)
<br>> )<br>><br>><br>><br>> On 2/11/07, mark <<a href="mailto:rkmr.em@gmail.com">rkmr.em@gmail.com</a>> wrote:<br>> > hi<br>> > are there any tools for importing the tiger database into postgres to use
<br>> with postgis?<br>> > thanks !<br>> > mark<br>> ><br>><br>><br>><br>> ________________________________<br>><br>><br>><br>><br>><br>><br>> The substance of this message, including any attachments, may be
<br>> confidential, legally privileged and/or exempt from disclosure<br>> pursuant to Massachusetts law. It is intended solely for the<br>> addressee. If you received this in error, please contact the sender<br>> and delete the material from any computer.
<br>><br>><br>><br>><br>><br>> ________________________________<br>><br>><br>><br>><br>><br>><br>> The substance of this message, including any attachments, may be<br>> confidential, legally privileged and/or exempt from disclosure
<br>> pursuant to Massachusetts law. It is intended solely for the<br>> addressee. If you received this in error, please contact the sender<br>> and delete the material from any computer.<br>><br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br><br><br>------------------------------<br><br>Message: 18<br>Date: Mon, 12 Feb 2007 16:47:39 -0000<br>From: "Pedro Doria Meunier" <
<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>><br>Subject: RE: [postgis-users] latitude / longitude from wkb_geometry<br>To: "'PostGIS Users Discussion'"<br> <<a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a>><br>Message-ID: <003401c74ec5$82715de0$875419a0$@com><br>Content-Type: text/plain; charset="us-ascii"<br><br>Hi Mark<br><br>Please see the previous email (to this one).
<br>pointn() starts at 1, not 0... ;-)<br><br>So the sql statement should be:<br>SELECT x(pointn(wkb_geometry,1)), y(pointn(wkb_geometry,1)) from masuf<br>where ogc_fid=62560;<br><br>This returns the first point of the linestring object.
<br><br>Cheers,<br>Pedro.<br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>Sent: segunda-feira, 12 de Fevereiro de 2007 16:42<br>To: PostGIS Users Discussion<br>Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
<br><br>Hi Pedro<br>Thanks for your replies.<br>I tried your SQL query on this row:<br><br>SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf<br>where ogc_fid=62560;<br>"62560";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C63247
<br>B7A5EC07DAEB6627FA54240";"TGR06085";"123181609";"";"O";"";"";"";"";"F10";"";<br>"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"6";"6";"85";"85";"92830";"9283
<br>0";"";"";"68000";"68000";"502601";"502601";"1019";"1019"<br><br>I just get Null values for x and y.<br><br>Can you tell what is wrong?
<br>thanks a lot!<br>mark<br><br><br>On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>> Hi Mark<br>><br>> Addendum to the previous post...<br>> I didn't notice that the geometry was LINESTRING :]
<br>> So what we need here is to concatenate some functions:<br>><br>> SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf;<br>><br>> This will give you the first coordinate of each line.
<br>><br>> Should you want to process all coordinates of each linestring here's a<br>> little PHP that might help:<br>><br>> // ------ cut here<br>> <?php<br>> $connection = pg_connect("host=yourhost port=5432 dbname=yourdb
<br>> user=yourusername password=yourpassword");<br>> if (!$connection) {<br>> print("Connection to the database failed.");<br>> exit;<br>> }<br>> /*<br>> Get all the records from the table.
<br>> We get the unique ogc_fid and the corresponding number of points for the<br>> linestring of this entry...<br>> */<br>> $sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";<br>> $myresult=pg_exec($connection, $sql);
<br>><br>> for ($row=0; $row<pg_numrows($myresult); $row++) {<br>> $unique=pg_result($myresult,$row,0);<br>> $npoints==pg_result($myresult,$row,0);<br>> // now we process each point in this entry
<br>> for ($point=0; $point<$npoints; $point++) {<br>> $sql= "SELECT x(pointn(wkb_geometry,$point)),<br>> y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";<br>
> $presult=pg_exec($connection, $sql);<br>> $lon=pg_result($presult,0,0);<br>> $lat=pg_result($presult,0,1);<br>> /*<br>> Do whatever you wish with $lon, $lat....<br>
> */<br>> }<br>> }<br>> ?><br>> // ------ cut here<br>><br>><br>> If the gurus out there have a more efficient way to do this, I'd be more<br>> than interested in hearing about it! ;-)
<br>><br>> HTH,<br>> Pedro Doria Meunier.<br>><br>><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net
</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> Sent: segunda-feira, 12 de Fevereiro de 2007 5:57<br>> To:
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> Subject: [postgis-users] latitude / longitude from wkb_geometry<br>><br>> how to get latitude and longitude from wkb_geometry?
<br>> My table structure is given below<br>> thanks<br>> mark<br>><br>> CREATE TABLE masuf<br>> (<br>> ogc_fid serial NOT NULL,<br>> wkb_geometry geometry,<br>> module char(8),<br>> tlid numeric(10),
<br>> side1 numeric(1),<br>> source char(1),<br>> fedirp char(2),<br>> fename char(30),<br>> fetype char(4),<br>> fedirs char(2),<br>> cfcc char(3),<br>> fraddl char(11),<br>> toaddl char(11),<br>
> fraddr char(11),<br>> toaddr char(11),<br>> friaddl char(1),<br>> toiaddl char(1),<br>> friaddr char(1),<br>> toiaddr char(1),<br>> zipl numeric(5),<br>> zipr numeric(5),<br>> aianhhfpl numeric(5),
<br>> aianhhfpr numeric(5),<br>> aihhtlil char(1),<br>> aihhtlir char(1),<br>> census1 char(1),<br>> census2 char(1),<br>> statel numeric(2),<br>> stater numeric(2),<br>> countyl numeric(3),<br>> countyr numeric(3),
<br>> cousubl numeric(5),<br>> cousubr numeric(5),<br>> submcdl numeric(5),<br>> submcdr numeric(5),<br>> placel numeric(5),<br>> placer numeric(5),<br>> tractl numeric(6),<br>> tractr numeric(6),<br>
> blockl numeric(4),<br>> blockr numeric(4),<br>> CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>> CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>> CONSTRAINT enforce_geotype_wkb_geometry CHECK
<br>> (geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>> NULL),<br>> CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>> )<br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>_______________________________________________
<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br><br><br><br><br>------------------------------<br><br>Message: 19<br>Date: Mon, 12 Feb 2007 10:55:28 -0600<br>From: "David William Bitner" <<a href="mailto:david.bitner@gmail.com">david.bitner@gmail.com
</a>><br>Subject: Re: [postgis-users] appending to a LINESTRING<br>To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID:
<br> <<a href="mailto:d2f53e190702120855n5ea46f33gd9a64bdfbb93409b@mail.gmail.com">d2f53e190702120855n5ea46f33gd9a64bdfbb93409b@mail.gmail.com</a>><br>Content-Type: text/plain; charset="iso-8859-1"<br>
<br>Update g_track set line=AddPoint(line, GeomFromText('POINT(4.92 52.35)',4326))<br>where id=101;<br><br>On 2/12/07, Just van den Broecke <<a href="mailto:just@justobjects.nl">just@justobjects.nl</a>> wrote:
<br>><br>> Hi,<br>><br>> What is the way to go for appending POINTs to a LINESTRING without<br>> reading the entire LINESTRING into the client ? i.e. for example<br>><br>> SELECT AddPoint(line, GeomFromText('POINT(
4.92 52.35)',4326)) as line<br>> FROM ( SELECT line FROM g_track WHERE id=101) foo;<br>><br>> Will do the append but will still require an UPDATE to be done with the<br>> resulting line. Would "one-shot" appending require a (plpg)sql function
<br>> or is there some other way ?<br>><br>> thanks,<br>><br>> --Just<br>><br>> Just van den Broecke<br>> <a href="http://www.justobjects.nl">http://www.justobjects.nl</a><br>> The Netherlands<br>
><br>><br>><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br><br><br><br>--<br>************************************<br>David William Bitner
<br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <a href="http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/7a457fff/attachment-0001.html">http://lists.refractions.net/pipermail/postgis-users/attachments/20070212/7a457fff/attachment-0001.html
</a><br><br>------------------------------<br><br>Message: 20<br>Date: Mon, 12 Feb 2007 09:08:47 -0800<br>From: mark <<a href="mailto:rkmr.em@gmail.com">rkmr.em@gmail.com</a>><br>Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
<br>To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID:<br> <<a href="mailto:e04d2e90702120908r1a589d45tbed75d4516fba258@mail.gmail.com">
e04d2e90702120908r1a589d45tbed75d4516fba258@mail.gmail.com</a>><br>Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br><br>Hi Pedro,<br>Oops! I missed the previous email! Thanks a lot!!!!<br><br>Now if I want to use this point in distance calculation should I need
<br>to convert it to geometry right? What SRID should I use? The SRID for<br>line string is 4269 and that is the only row I have in<br>geometry_columns table.<br><br>select geometryfromtext('POINT(-121.913666 37.292952
)', SRID???)<br>Can I use the same SRID?<br>PostGIS rocsk!!!<br>Thanks a lot!<br>mark<br><br>On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>> Hi Mark
<br>><br>> Please see the previous email (to this one).<br>> pointn() starts at 1, not 0... ;-)<br>><br>> So the sql statement should be:<br>> SELECT x(pointn(wkb_geometry,1)), y(pointn(wkb_geometry,1)) from masuf
<br>> where ogc_fid=62560;<br>><br>> This returns the first point of the linestring object.<br>><br>> Cheers,<br>> Pedro.<br>><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> Sent: segunda-feira, 12 de Fevereiro de 2007 16:42
<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] latitude / longitude from wkb_geometry<br>><br>> Hi Pedro<br>> Thanks for your replies.<br>> I tried your SQL query on this row:<br>>
<br>> SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf<br>> where ogc_fid=62560;<br>> "62560";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C63247<br>> B7A5EC07DAEB6627FA54240";"TGR06085";"123181609";"";"O";"";"";"";"";"F10";"";
<br>> "";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"6";"6";"85";"85";"92830";"9283
<br>> 0";"";"";"68000";"68000";"502601";"502601";"1019";"1019"<br>><br>> I just get Null values for x and y.<br>><br>> Can you tell what is wrong?
<br>> thanks a lot!<br>> mark<br>><br>><br>> On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>> > Hi Mark<br>> ><br>> > Addendum to the previous post...
<br>> > I didn't notice that the geometry was LINESTRING :]<br>> > So what we need here is to concatenate some functions:<br>> ><br>> > SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf;
<br>> ><br>> > This will give you the first coordinate of each line.<br>> ><br>> > Should you want to process all coordinates of each linestring here's a<br>> > little PHP that might help:
<br>> ><br>> > // ------ cut here<br>> > <?php<br>> > $connection = pg_connect("host=yourhost port=5432 dbname=yourdb<br>> > user=yourusername password=yourpassword");<br>> > if (!$connection) {
<br>> > print("Connection to the database failed.");<br>> > exit;<br>> > }<br>> > /*<br>> > Get all the records from the table.<br>> > We get the unique ogc_fid and the corresponding number of points for the
<br>> > linestring of this entry...<br>> > */<br>> > $sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";<br>> > $myresult=pg_exec($connection, $sql);<br>> ><br>> > for ($row=0; $row<pg_numrows($myresult); $row++) {
<br>> > $unique=pg_result($myresult,$row,0);<br>> > $npoints==pg_result($myresult,$row,0);<br>> > // now we process each point in this entry<br>> > for ($point=0; $point<$npoints; $point++) {
<br>> > $sql= "SELECT x(pointn(wkb_geometry,$point)),<br>> > y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";<br>> > $presult=pg_exec($connection, $sql);
<br>> > $lon=pg_result($presult,0,0);<br>> > $lat=pg_result($presult,0,1);<br>> > /*<br>> > Do whatever you wish with $lon, $lat....<br>> > */<br>> > }
<br>> > }<br>> > ?><br>> > // ------ cut here<br>> ><br>> ><br>> > If the gurus out there have a more efficient way to do this, I'd be more<br>> > than interested in hearing about it! ;-)
<br>> ><br>> > HTH,<br>> > Pedro Doria Meunier.<br>> ><br>> ><br>> > -----Original Message-----<br>> > From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net
</a><br>> > [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> > Sent: segunda-feira, 12 de Fevereiro de 2007 5:57<br>
> > To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> > Subject: [postgis-users] latitude / longitude from wkb_geometry<br>> ><br>> > how to get latitude and longitude from wkb_geometry?
<br>> > My table structure is given below<br>> > thanks<br>> > mark<br>> ><br>> > CREATE TABLE masuf<br>> > (<br>> > ogc_fid serial NOT NULL,<br>> > wkb_geometry geometry,<br>
> > module char(8),<br>> > tlid numeric(10),<br>> > side1 numeric(1),<br>> > source char(1),<br>> > fedirp char(2),<br>> > fename char(30),<br>> > fetype char(4),<br>> > fedirs char(2),
<br>> > cfcc char(3),<br>> > fraddl char(11),<br>> > toaddl char(11),<br>> > fraddr char(11),<br>> > toaddr char(11),<br>> > friaddl char(1),<br>> > toiaddl char(1),<br>> > friaddr char(1),
<br>> > toiaddr char(1),<br>> > zipl numeric(5),<br>> > zipr numeric(5),<br>> > aianhhfpl numeric(5),<br>> > aianhhfpr numeric(5),<br>> > aihhtlil char(1),<br>> > aihhtlir char(1),
<br>> > census1 char(1),<br>> > census2 char(1),<br>> > statel numeric(2),<br>> > stater numeric(2),<br>> > countyl numeric(3),<br>> > countyr numeric(3),<br>> > cousubl numeric(5),
<br>> > cousubr numeric(5),<br>> > submcdl numeric(5),<br>> > submcdr numeric(5),<br>> > placel numeric(5),<br>> > placer numeric(5),<br>> > tractl numeric(6),<br>> > tractr numeric(6),
<br>> > blockl numeric(4),<br>> > blockr numeric(4),<br>> > CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>> > CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>> > CONSTRAINT enforce_geotype_wkb_geometry CHECK
<br>> > (geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>> > NULL),<br>> > CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>> > )<br>> > _______________________________________________
<br>> > postgis-users mailing list<br>> > <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>> ><br>> ><br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>> ><br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br><br><br>------------------------------
<br><br>Message: 21<br>Date: Mon, 12 Feb 2007 09:07:52 -0800<br>From: Emily Gouge <<a href="mailto:egouge@refractions.net">egouge@refractions.net</a>><br>Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
<br>To: PostGIS Users Discussion <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: <<a href="mailto:45D09EE8.2030901@refractions.net">45D09EE8.2030901@refractions.net
</a>><br>Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br><br>If you want to extract all the points in a linestring with a single query you can try the<br>generate_series function:<br><br>For Example:<br>
SELECT astext( pointn(the_geom, generate_series(1, numpoints(the_geom))))<br>FROM (select geomfromtext('LINESTRING(0 0, 1 1, 2 2)') as the_geom) as foo;<br><br>Returns:<br> astext<br>------------<br> POINT(0 0)
<br> POINT(1 1)<br> POINT(2 2)<br>(3 rows)<br><br><br>Pedro Doria Meunier wrote:<br>> Hi Mark<br>><br>> Please see the previous email (to this one).<br>> pointn() starts at 1, not 0... ;-)<br>><br>> So the sql statement should be:
<br>> SELECT x(pointn(wkb_geometry,1)), y(pointn(wkb_geometry,1)) from masuf<br>> where ogc_fid=62560;<br>><br>> This returns the first point of the linestring object.<br>><br>> Cheers,<br>> Pedro.<br>
><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> Sent: segunda-feira, 12 de Fevereiro de 2007 16:42<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
<br>><br>> Hi Pedro<br>> Thanks for your replies.<br>> I tried your SQL query on this row:<br>><br>> SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf<br>> where ogc_fid=62560;<br>
> "62560";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C63247<br>> B7A5EC07DAEB6627FA54240";"TGR06085";"123181609";"";"O";"";"";"";"";"F10";"";
<br>> "";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"6";"6";"85";"85";"92830";"9283
<br>> 0";"";"";"68000";"68000";"502601";"502601";"1019";"1019"<br>><br>> I just get Null values for x and y.<br>><br>> Can you tell what is wrong?
<br>> thanks a lot!<br>> mark<br>><br>><br>> On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>>> Hi Mark<br>>><br>>> Addendum to the previous post...
<br>>> I didn't notice that the geometry was LINESTRING :]<br>>> So what we need here is to concatenate some functions:<br>>><br>>> SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf;
<br>>><br>>> This will give you the first coordinate of each line.<br>>><br>>> Should you want to process all coordinates of each linestring here's a<br>>> little PHP that might help:<br>
>><br>>> // ------ cut here<br>>> <?php<br>>> $connection = pg_connect("host=yourhost port=5432 dbname=yourdb<br>>> user=yourusername password=yourpassword");<br>>> if (!$connection) {
<br>>> print("Connection to the database failed.");<br>>> exit;<br>>> }<br>>> /*<br>>> Get all the records from the table.<br>>> We get the unique ogc_fid and the corresponding number of points for the
<br>>> linestring of this entry...<br>>> */<br>>> $sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";<br>>> $myresult=pg_exec($connection, $sql);<br>>><br>>> for ($row=0; $row<pg_numrows($myresult); $row++) {
<br>>> $unique=pg_result($myresult,$row,0);<br>>> $npoints==pg_result($myresult,$row,0);<br>>> // now we process each point in this entry<br>>> for ($point=0; $point<$npoints; $point++) {
<br>>> $sql= "SELECT x(pointn(wkb_geometry,$point)),<br>>> y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";<br>>> $presult=pg_exec($connection, $sql);
<br>>> $lon=pg_result($presult,0,0);<br>>> $lat=pg_result($presult,0,1);<br>>> /*<br>>> Do whatever you wish with $lon, $lat....<br>>> */<br>>> }
<br>>> }<br>>> ?><br>>> // ------ cut here<br>>><br>>><br>>> If the gurus out there have a more efficient way to do this, I'd be more<br>>> than interested in hearing about it! ;-)
<br>>><br>>> HTH,<br>>> Pedro Doria Meunier.<br>>><br>>><br>>> -----Original Message-----<br>>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net
</a><br>>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>>> Sent: segunda-feira, 12 de Fevereiro de 2007 5:57<br>
>> To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> Subject: [postgis-users] latitude / longitude from wkb_geometry<br>>><br>>> how to get latitude and longitude from wkb_geometry?
<br>>> My table structure is given below<br>>> thanks<br>>> mark<br>>><br>>> CREATE TABLE masuf<br>>> (<br>>> ogc_fid serial NOT NULL,<br>>> wkb_geometry geometry,<br>>> module char(8),
<br>>> tlid numeric(10),<br>>> side1 numeric(1),<br>>> source char(1),<br>>> fedirp char(2),<br>>> fename char(30),<br>>> fetype char(4),<br>>> fedirs char(2),<br>>> cfcc char(3),
<br>>> fraddl char(11),<br>>> toaddl char(11),<br>>> fraddr char(11),<br>>> toaddr char(11),<br>>> friaddl char(1),<br>>> toiaddl char(1),<br>>> friaddr char(1),<br>>> toiaddr char(1),
<br>>> zipl numeric(5),<br>>> zipr numeric(5),<br>>> aianhhfpl numeric(5),<br>>> aianhhfpr numeric(5),<br>>> aihhtlil char(1),<br>>> aihhtlir char(1),<br>>> census1 char(1),<br>>> census2 char(1),
<br>>> statel numeric(2),<br>>> stater numeric(2),<br>>> countyl numeric(3),<br>>> countyr numeric(3),<br>>> cousubl numeric(5),<br>>> cousubr numeric(5),<br>>> submcdl numeric(5),
<br>>> submcdr numeric(5),<br>>> placel numeric(5),<br>>> placer numeric(5),<br>>> tractl numeric(6),<br>>> tractr numeric(6),<br>>> blockl numeric(4),<br>>> blockr numeric(4),<br>
>> CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>>> CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>>> CONSTRAINT enforce_geotype_wkb_geometry CHECK<br>>> (geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS
<br>>> NULL),<br>>> CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>>> )<br>>> _______________________________________________<br>>> postgis-users mailing list<br>>>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br>>><br>>><br>>> _______________________________________________<br>>> postgis-users mailing list<br>>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>>><br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br><br>------------------------------
<br><br>Message: 22<br>Date: Mon, 12 Feb 2007 17:20:29 -0000<br>From: "Pedro Doria Meunier" <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>><br>Subject: RE: [postgis-users] latitude / longitude from wkb_geometry
<br>To: "'PostGIS Users Discussion'"<br> <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID: <003b01c74eca$1cdf8740$569e95c0$@com
><br>Content-Type: text/plain; charset="us-ascii"<br><br>Txs Emily!<br><br>Sharply done!<br><br>Pedro.<br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Emily<br>Gouge<br>Sent: segunda-feira, 12 de Fevereiro de 2007 17:08
<br>To: PostGIS Users Discussion<br>Subject: Re: [postgis-users] latitude / longitude from wkb_geometry<br><br>If you want to extract all the points in a linestring with a single query<br>you can try the<br>generate_series function:
<br><br>For Example:<br>SELECT astext( pointn(the_geom, generate_series(1, numpoints(the_geom))))<br>FROM (select geomfromtext('LINESTRING(0 0, 1 1, 2 2)') as the_geom) as foo;<br><br>Returns:<br> astext<br>------------
<br> POINT(0 0)<br> POINT(1 1)<br> POINT(2 2)<br>(3 rows)<br><br><br>Pedro Doria Meunier wrote:<br>> Hi Mark<br>><br>> Please see the previous email (to this one).<br>> pointn() starts at 1, not 0... ;-)<br>
><br>> So the sql statement should be:<br>> SELECT x(pointn(wkb_geometry,1)), y(pointn(wkb_geometry,1)) from masuf<br>> where ogc_fid=62560;<br>><br>> This returns the first point of the linestring object.
<br>><br>> Cheers,<br>> Pedro.<br>><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>> [mailto:
<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> Sent: segunda-feira, 12 de Fevereiro de 2007 16:42<br>> To: PostGIS Users Discussion
<br>> Subject: Re: [postgis-users] latitude / longitude from wkb_geometry<br>><br>> Hi Pedro<br>> Thanks for your replies.<br>> I tried your SQL query on this row:<br>><br>> SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf
<br>> where ogc_fid=62560;<br>><br>"62560";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C63247<br>><br>B7A5EC07DAEB6627FA54240";"TGR06085";"123181609";"";"O";"";"";"";"";"F10";"";
<br>><br>"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"6";"6";"85";"85";"92830";"9283
<br>> 0";"";"";"68000";"68000";"502601";"502601";"1019";"1019"<br>><br>> I just get Null values for x and y.<br>><br>> Can you tell what is wrong?
<br>> thanks a lot!<br>> mark<br>><br>><br>> On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>>> Hi Mark<br>>><br>>> Addendum to the previous post...
<br>>> I didn't notice that the geometry was LINESTRING :]<br>>> So what we need here is to concatenate some functions:<br>>><br>>> SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf;
<br>>><br>>> This will give you the first coordinate of each line.<br>>><br>>> Should you want to process all coordinates of each linestring here's a<br>>> little PHP that might help:<br>
>><br>>> // ------ cut here<br>>> <?php<br>>> $connection = pg_connect("host=yourhost port=5432 dbname=yourdb<br>>> user=yourusername password=yourpassword");<br>>> if (!$connection) {
<br>>> print("Connection to the database failed.");<br>>> exit;<br>>> }<br>>> /*<br>>> Get all the records from the table.<br>>> We get the unique ogc_fid and the corresponding number of points for the
<br>>> linestring of this entry...<br>>> */<br>>> $sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";<br>>> $myresult=pg_exec($connection, $sql);<br>>><br>>> for ($row=0; $row<pg_numrows($myresult); $row++) {
<br>>> $unique=pg_result($myresult,$row,0);<br>>> $npoints==pg_result($myresult,$row,0);<br>>> // now we process each point in this entry<br>>> for ($point=0; $point<$npoints; $point++) {
<br>>> $sql= "SELECT x(pointn(wkb_geometry,$point)),<br>>> y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";<br>>> $presult=pg_exec($connection, $sql);
<br>>> $lon=pg_result($presult,0,0);<br>>> $lat=pg_result($presult,0,1);<br>>> /*<br>>> Do whatever you wish with $lon, $lat....<br>>> */<br>>> }
<br>>> }<br>>> ?><br>>> // ------ cut here<br>>><br>>><br>>> If the gurus out there have a more efficient way to do this, I'd be more<br>>> than interested in hearing about it! ;-)
<br>>><br>>> HTH,<br>>> Pedro Doria Meunier.<br>>><br>>><br>>> -----Original Message-----<br>>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net
</a><br>>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>>> Sent: segunda-feira, 12 de Fevereiro de 2007 5:57<br>
>> To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> Subject: [postgis-users] latitude / longitude from wkb_geometry<br>>><br>>> how to get latitude and longitude from wkb_geometry?
<br>>> My table structure is given below<br>>> thanks<br>>> mark<br>>><br>>> CREATE TABLE masuf<br>>> (<br>>> ogc_fid serial NOT NULL,<br>>> wkb_geometry geometry,<br>>> module char(8),
<br>>> tlid numeric(10),<br>>> side1 numeric(1),<br>>> source char(1),<br>>> fedirp char(2),<br>>> fename char(30),<br>>> fetype char(4),<br>>> fedirs char(2),<br>>> cfcc char(3),
<br>>> fraddl char(11),<br>>> toaddl char(11),<br>>> fraddr char(11),<br>>> toaddr char(11),<br>>> friaddl char(1),<br>>> toiaddl char(1),<br>>> friaddr char(1),<br>>> toiaddr char(1),
<br>>> zipl numeric(5),<br>>> zipr numeric(5),<br>>> aianhhfpl numeric(5),<br>>> aianhhfpr numeric(5),<br>>> aihhtlil char(1),<br>>> aihhtlir char(1),<br>>> census1 char(1),<br>>> census2 char(1),
<br>>> statel numeric(2),<br>>> stater numeric(2),<br>>> countyl numeric(3),<br>>> countyr numeric(3),<br>>> cousubl numeric(5),<br>>> cousubr numeric(5),<br>>> submcdl numeric(5),
<br>>> submcdr numeric(5),<br>>> placel numeric(5),<br>>> placer numeric(5),<br>>> tractl numeric(6),<br>>> tractr numeric(6),<br>>> blockl numeric(4),<br>>> blockr numeric(4),<br>
>> CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>>> CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>>> CONSTRAINT enforce_geotype_wkb_geometry CHECK<br>>> (geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS
<br>>> NULL),<br>>> CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>>> )<br>>> _______________________________________________<br>>> postgis-users mailing list<br>>>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br>>><br>>><br>>> _______________________________________________<br>>> postgis-users mailing list<br>>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>>><br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br>_______________________________________________
<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br><br><br><br><br>------------------------------<br><br>Message: 23<br>Date: Mon, 12 Feb 2007 17:53:30 -0000<br>From: "Pedro Doria Meunier" <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>
><br>Subject: RE: [postgis-users] latitude / longitude from wkb_geometry<br>To: "'PostGIS Users Discussion'"<br> <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a>><br>Message-ID: <003c01c74ece$b72eb6f0$258c24d0$@com><br>Content-Type: text/plain; charset="us-ascii"<br><br>Hi Mark<br><br>The distance function takes two parameters: distance(geometry, geometry)
<br><br>It'll give you values based on the geometry's SRID.<br>(And you shouldn't mix two different SRIDs when calling the function -- I<br>expect that an exception would be raised...)<br><br>For SRID==4269 values are returned in
ddd.dddd (degrees) (GEOGCS).<br><br>Should you want to work in meters you must transform it to a projected<br>coordinate system.<br><br>You can use the longitude to get the desired zone. Here's the formula:<br>utmzone = ((lon + 180) / 6) + 1
<br><br>Having obtained the desired utm zone the following sql statement returns the<br>srid:<br><br>SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS 84 / UTM<br>zone $utm%' LIMIT 1;<br>Note: $utm is a variable, in this case PHP is being used... substitute with
<br>the value found above<br>Note1: the '%' is a metacharacter. In this case it 'validates' anything<br>following the utm...<br><br>In your case the returned SRID would be 32651 (using -121.913666 as the lon)
<br><br>All this ends in this sql statement:<br>SELECT distance(transform(wkb_geometry,32651),<br>transform(geometryfromtext('POINT(-121.913666 37.292952)', 4269),32651) from<br>masuf;<br><br>You can then pick the returned value and convert it to miles, whatever...
<br><br>HTH,<br>Pedro Doria Meunier.<br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>Sent: segunda-feira, 12 de Fevereiro de 2007 17:09<br>To: PostGIS Users Discussion<br>Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
<br><br>Hi Pedro,<br>Oops! I missed the previous email! Thanks a lot!!!!<br><br>Now if I want to use this point in distance calculation should I need<br>to convert it to geometry right? What SRID should I use? The SRID for
<br>line string is 4269 and that is the only row I have in<br>geometry_columns table.<br><br>select geometryfromtext('POINT(-121.913666 37.292952)', SRID???)<br>Can I use the same SRID?<br>PostGIS rocsk!!!<br>Thanks a lot!
<br>mark<br><br>On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>> Hi Mark<br>><br>> Please see the previous email (to this one).<br>> pointn() starts at 1, not 0... ;-)
<br>><br>> So the sql statement should be:<br>> SELECT x(pointn(wkb_geometry,1)), y(pointn(wkb_geometry,1)) from masuf<br>> where ogc_fid=62560;<br>><br>> This returns the first point of the linestring object.
<br>><br>> Cheers,<br>> Pedro.<br>><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>> [mailto:
<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> Sent: segunda-feira, 12 de Fevereiro de 2007 16:42<br>> To: PostGIS Users Discussion
<br>> Subject: Re: [postgis-users] latitude / longitude from wkb_geometry<br>><br>> Hi Pedro<br>> Thanks for your replies.<br>> I tried your SQL query on this row:<br>><br>> SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf
<br>> where ogc_fid=62560;<br>><br>"62560";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C63247<br>><br>B7A5EC07DAEB6627FA54240";"TGR06085";"123181609";"";"O";"";"";"";"";"F10";"";
<br>><br>"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"6";"6";"85";"85";"92830";"9283
<br>> 0";"";"";"68000";"68000";"502601";"502601";"1019";"1019"<br>><br>> I just get Null values for x and y.<br>><br>> Can you tell what is wrong?
<br>> thanks a lot!<br>> mark<br>><br>><br>> On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>> > Hi Mark<br>> ><br>> > Addendum to the previous post...
<br>> > I didn't notice that the geometry was LINESTRING :]<br>> > So what we need here is to concatenate some functions:<br>> ><br>> > SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from masuf;
<br>> ><br>> > This will give you the first coordinate of each line.<br>> ><br>> > Should you want to process all coordinates of each linestring here's a<br>> > little PHP that might help:
<br>> ><br>> > // ------ cut here<br>> > <?php<br>> > $connection = pg_connect("host=yourhost port=5432 dbname=yourdb<br>> > user=yourusername password=yourpassword");<br>> > if (!$connection) {
<br>> > print("Connection to the database failed.");<br>> > exit;<br>> > }<br>> > /*<br>> > Get all the records from the table.<br>> > We get the unique ogc_fid and the corresponding number of points for the
<br>> > linestring of this entry...<br>> > */<br>> > $sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";<br>> > $myresult=pg_exec($connection, $sql);<br>> ><br>> > for ($row=0; $row<pg_numrows($myresult); $row++) {
<br>> > $unique=pg_result($myresult,$row,0);<br>> > $npoints==pg_result($myresult,$row,0);<br>> > // now we process each point in this entry<br>> > for ($point=0; $point<$npoints; $point++) {
<br>> > $sql= "SELECT x(pointn(wkb_geometry,$point)),<br>> > y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";<br>> > $presult=pg_exec($connection, $sql);
<br>> > $lon=pg_result($presult,0,0);<br>> > $lat=pg_result($presult,0,1);<br>> > /*<br>> > Do whatever you wish with $lon, $lat....<br>> > */<br>> > }
<br>> > }<br>> > ?><br>> > // ------ cut here<br>> ><br>> ><br>> > If the gurus out there have a more efficient way to do this, I'd be more<br>> > than interested in hearing about it! ;-)
<br>> ><br>> > HTH,<br>> > Pedro Doria Meunier.<br>> ><br>> ><br>> > -----Original Message-----<br>> > From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net
</a><br>> > [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> > Sent: segunda-feira, 12 de Fevereiro de 2007 5:57<br>
> > To: <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> > Subject: [postgis-users] latitude / longitude from wkb_geometry<br>> ><br>> > how to get latitude and longitude from wkb_geometry?
<br>> > My table structure is given below<br>> > thanks<br>> > mark<br>> ><br>> > CREATE TABLE masuf<br>> > (<br>> > ogc_fid serial NOT NULL,<br>> > wkb_geometry geometry,<br>
> > module char(8),<br>> > tlid numeric(10),<br>> > side1 numeric(1),<br>> > source char(1),<br>> > fedirp char(2),<br>> > fename char(30),<br>> > fetype char(4),<br>> > fedirs char(2),
<br>> > cfcc char(3),<br>> > fraddl char(11),<br>> > toaddl char(11),<br>> > fraddr char(11),<br>> > toaddr char(11),<br>> > friaddl char(1),<br>> > toiaddl char(1),<br>> > friaddr char(1),
<br>> > toiaddr char(1),<br>> > zipl numeric(5),<br>> > zipr numeric(5),<br>> > aianhhfpl numeric(5),<br>> > aianhhfpr numeric(5),<br>> > aihhtlil char(1),<br>> > aihhtlir char(1),
<br>> > census1 char(1),<br>> > census2 char(1),<br>> > statel numeric(2),<br>> > stater numeric(2),<br>> > countyl numeric(3),<br>> > countyr numeric(3),<br>> > cousubl numeric(5),
<br>> > cousubr numeric(5),<br>> > submcdl numeric(5),<br>> > submcdr numeric(5),<br>> > placel numeric(5),<br>> > placer numeric(5),<br>> > tractl numeric(6),<br>> > tractr numeric(6),
<br>> > blockl numeric(4),<br>> > blockr numeric(4),<br>> > CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<br>> > CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<br>> > CONSTRAINT enforce_geotype_wkb_geometry CHECK
<br>> > (geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<br>> > NULL),<br>> > CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<br>> > )<br>> > _______________________________________________
<br>> > postgis-users mailing list<br>> > <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>> ><br>> ><br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>> ><br>> _______________________________________________
<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>_______________________________________________
<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br><br><br><br><br>------------------------------<br><br>Message: 24<br>Date: Mon, 12 Feb 2007 11:13:56 -0800<br>From: mark <<a href="mailto:rkmr.em@gmail.com">rkmr.em@gmail.com</a>><br>Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
<br>To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>Message-ID:<br> <<a href="mailto:e04d2e90702121113u3ed17d9cobc9fdc3a72926ac7@mail.gmail.com">
e04d2e90702121113u3ed17d9cobc9fdc3a72926ac7@mail.gmail.com</a>><br>Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br><br>Hi Pedro,<br>Thanks a ton!!!<br>Will try all of this out!!!!<br><br>My location data is a US street address. Can you tell me what is the
<br>best way to get to a POINT from the US Street address? I am<br>considering searching the masuf table that I have which I have<br>populated with TIGER database . If I map the US street address to a<br>row I get the line geometry from which I can get approximage point
<br>data.<br><br>Thanks a lot<br>mark<br><br><br><br>On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>> Hi Mark<br>><br>> The distance function takes two parameters: distance(geometry, geometry)
<br>><br>> It'll give you values based on the geometry's SRID.<br>> (And you shouldn't mix two different SRIDs when calling the function -- I<br>> expect that an exception would be raised...)<br>>
<br>> For SRID==4269 values are returned in ddd.dddd (degrees) (GEOGCS).<br>><br>> Should you want to work in meters you must transform it to a projected<br>> coordinate system.<br>><br>> You can use the longitude to get the desired zone. Here's the formula:
<br>> utmzone = ((lon + 180) / 6) + 1<br>><br>> Having obtained the desired utm zone the following sql statement returns the<br>> srid:<br>><br>> SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS 84 / UTM
<br>> zone $utm%' LIMIT 1;<br>> Note: $utm is a variable, in this case PHP is being used... substitute with<br>> the value found above<br>> Note1: the '%' is a metacharacter. In this case it 'validates' anything
<br>> following the utm...<br>><br>> In your case the returned SRID would be 32651 (using -121.913666 as the lon)<br>><br>> All this ends in this sql statement:<br>> SELECT distance(transform(wkb_geometry,32651),
<br>> transform(geometryfromtext('POINT(-121.913666 37.292952)', 4269),32651) from<br>> masuf;<br>><br>> You can then pick the returned value and convert it to miles, whatever...<br>><br>> HTH,<br>
> Pedro Doria Meunier.<br>><br>> -----Original Message-----<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">
postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of mark<br>> Sent: segunda-feira, 12 de Fevereiro de 2007 17:09<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
<br>><br>> Hi Pedro,<br>> Oops! I missed the previous email! Thanks a lot!!!!<br>><br>> Now if I want to use this point in distance calculation should I need<br>> to convert it to geometry right? What SRID should I use? The SRID for
<br>> line string is 4269 and that is the only row I have in<br>> geometry_columns table.<br>><br>> select geometryfromtext('POINT(-121.913666 37.292952)', SRID???)<br>> Can I use the same SRID?<br>
> PostGIS rocsk!!!<br>> Thanks a lot!<br>> mark<br>><br>> On 2/12/07, Pedro Doria Meunier <<a href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</a>> wrote:<br>> > Hi Mark<br>> ><br>
> > Please see the previous email (to this one).<br>> > pointn() starts at 1, not 0... ;-)<br>> ><br>> > So the sql statement should be:<br>> > SELECT x(pointn(wkb_geometry,1)), y(pointn(wkb_geometry,1)) from masuf
<br>> > where ogc_fid=62560;<br>> ><br>> > This returns the first point of the linestring object.<br>> ><br>> > Cheers,<br>> > Pedro.<br>> ><br>> > -----Original Message-----
<br>> > From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>> > [mailto:<a href="mailto:post.">post.</a>..<br><br>[Message clipped] </blockquote>
</div><br><br clear="all"><br>-- <br>Regards,<br><br>Mark Thomas<br><a href="mailto:spatialguru.net@gmail.com">spatialguru.net@gmail.com</a><br>205.529.9013<br><br>"Commit to the Lord whatever you do,<br> and your plans will succeed." - Proverbs 16:3