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