<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16414" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>I think the MakeLine function might do the trick for
you.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>Something like</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>select makeline(b.the_geom) as
theline, b.trip </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>FROM (SELECT the_geom, trip FROM coords
ORDER BY trip, id) b </SPAN></FONT><FONT face=Arial color=#0000ff
size=2><SPAN class=347395114-12042007><BR>group by b.trip</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>trip is just an arbitrary field I made up which
you may not have. For example if you have points for a certain visit
denoted by trip, Then to get a linestring path for each trip, you would do
the above.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>If you just did </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>select makeline(b.the_geom) as
theline, b.trip </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>FROM (SELECT the_geom FROM coords ORDER BY id)
b </SPAN></FONT><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007><BR></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>then you would get a single record that collapses
all your points into a single linestring which could get pretty huge depending
on how many points you have.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>The subselect is to force the ordering of the points
since makeline will plot the points in the order they appear in the result
set.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>Hope that helps,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007>Regina</DIV></SPAN></FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=347395114-12042007></SPAN></FONT> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Giuseppe Molinario<BR><B>Sent:</B> Thursday, April 12, 2007 10:04
AM<BR><B>To:</B> Brent Wood<BR><B>Cc:</B> PostGIS Users
Discussion<BR><B>Subject:</B> Re: [postgis-users] Latitude and Longitude format
in PostGIS<BR></FONT><BR></DIV>
<DIV></DIV>Brent, <BR><BR>Thank you - We are using the Sql code you gave us and
it is working good. We've managed to automate the Lat/long coordinates from an
entering gprmc format email right to our Web-GIS service.<BR><BR><BR>Our problem
now, that we are dealing with, is the creation of a line from the inserted
points. Do we really have to go back and use the linestring from text function?
converting again the lat/long coordinates? Or do can we just use a Postgis
function that "Connects the dots" from the postgis format (have a geom column)
points that are already in the DB? <BR><BR>Thanks again for th help
guys!<BR><BR>Giuseppe<BR><BR><BR> <BR><BR>
<DIV><SPAN class=gmail_quote>On 4/12/07, <B class=gmail_sendername>Brent
Wood</B> <<A href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</A> >
wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid"><BR>---
Giuseppe Molinario <<A
href="mailto:g.molinario@gmail.com">g.molinario@gmail.com </A>>
wrote:<BR><BR>You can use (I think) the proj binary to convert the dddmm.mmm
to dd.dddd, but<BR>it may be easier to use an SQL with the data in Postgis
tables as numeric data.<BR><BR>The reformatting of ddmm.mmmm to dd.ddddd is
not technically reprojecting.<BR><BR>The following is one way to do this, by
inserting your ddmmm.mmmm values, then<BR>generating a point geometry from
these with an update SQL.<BR><BR><BR>ie, along the lines of: <BR><BR># create
your table...<BR>create table coords<BR> (id serial
primary key,<BR>
x decimal(10,5),<BR>
y decinal(10,5))<BR><BR>load your XY data...<BR>insert
into coords values (default, 16778.8888, 4234.6667 );<BR><BR># add a geometry
column to the table<BR>select
addgeometrycolumn('','coords','the_geom',4326,'POINT',2);<BR><BR>#populate the
geometry column<BR>update coords<BR>set
the_geom=geomfromtext('POINT('||floor(x/100)+((x/100-floor(x/100))/0.60)||'
<BR>'||floor(y/100)+((y/100-floor(y/100))/0.60)||')',4326);<BR><BR>select id,
x, y, astext(the_geom) from coords;<BR>id
| x |
y | astext<BR>----+-------------+------------+------------------------------------------
<BR> 3 | 16778.88880 | 4234.66670 | POINT(168.314813333333
42.5777783333333)<BR>(1 row)<BR><BR><BR>note that floor as used above will not
work with negative coords, but you can<BR>still use a similar approach using
strings, or ceil/int & a where clause for <BR>negative
coords....<BR><BR>If you are comfortable writing a postgres function to do
this conversion, that<BR>is also
possible.<BR><BR><BR>Cheers,<BR><BR> Brent Wood<BR><BR>> Hi
Mark,<BR>><BR>> Yes, that helps. I'll see if i missed the proj4
installation along the way, <BR>> or if, more probably i am simply feeding
PostGIS lat/long data that is not<BR>> in decimal degrees.<BR>><BR>>
I'll let you know if it doesn't work.<BR>><BR>> Thanks,<BR>><BR>>
Giuseppe <BR>><BR>><BR>><BR>> On 4/11/07, Mark Cave-Ayland <<A
href="mailto:mark.cave-ayland@ilande.co.uk">mark.cave-ayland@ilande.co.uk</A>>
wrote:<BR>> ><BR>> > On Wed, 2007-04-11 at 13:58 +0200, Giuseppe
Molinario wrote: <BR>> > > Hi everyone,<BR>> ><BR>> > Hi
Guiseppe,<BR>> ><BR>> > > I have a question regarding the
creation in PostGIS of a table that<BR>> > > contains information
coming from a NMEA GPRMC text string. I get <BR>> > > Lat/long
coordinates which are then put into their respective columns.<BR>> >
> A geometry field is then created using the SRID # 4326 (WGS84 Datum
-<BR>> > > Geographic).<BR>> > > <BR>> > > 1. What
format of Lat/long coordinates does PostGIS accept? GPRMC has<BR>> >
> the coordinate in this format: ddmm.mmmmm.<BR>> ><BR>> >
PostGIS accepts coordinates in decimal degrees. <BR>> ><BR>> >
> 2. How do I project the points into WGS84UTM33N? My final goal is
to<BR>> > > overlay these points as a PostGIS layer on a webmap
(Mapserver based)<BR>> > > over other data I have which is in that
projection. <BR>> ><BR>> > The transform() function should be able
to do what you need here if you<BR>> > use a version of PostGIS that has
been compiled with the PROJ.4<BR>> > reprojection library.<BR>> >
<BR>> > > 3. Do you know if Mapserver will re-project on the fly in
my case?<BR>> > > ever? or do I have to take care of this in
PostGIS?<BR>> ><BR>> > Yes. Mapserver can also reproject layers
on-the-fly using PROJ.4. There<BR>> > are plenty of examples on the
Mapserver website at<BR>> > <A
href="http://mapserver.gis.umn.edu">http://mapserver.gis.umn.edu</A>.<BR>>
><BR>> ><BR>> > HTH,<BR>> ><BR>> > Mark.<BR>>
><BR>> ><BR>> >
_______________________________________________<BR>> > postgis-users
mailing list<BR>> > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</A><BR>> > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
><BR>> > _______________________________________________ <BR>>
postgis-users mailing list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR><BR></BLOCKQUOTE></DIV><BR></BODY></HTML>
<HTML><BODY><P><hr size=1></P><br>
<P><STRONG><br>
The substance of this message, including any attachments, may be<br>
confidential, legally privileged and/or exempt from disclosure<br>
pursuant to Massachusetts law. It is intended solely for the<br>
addressee. If you received this in error, please contact the sender<br>
and delete the material from any computer.<br>
</STRONG></P></BODY></HTML>