[postgis-users] latitude / longitude from wkb_geometry

[mark]

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
>