'Re: [postgis-users] How to create a point geometry from two text'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] How to create a point geometry from two text
From:       pcreso () pcreso ! com
Date:       2011-03-25 16:44:30
Message-ID: 855806.16983.qm () web33208 ! mail ! mud ! yahoo ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

Concatenation of strings & geomfromtext or cast to numeric & makepoint, I'm not sure \
which would be faster, but both will work.

So (as below)  
update mytable set the_geom=ST_GeometryFromText('POINT(' || long || ' ' || lat || \
')',4326)

or 

update mytable set the_geom=setsrid(makepoint(long::numeric(7,4), \
lat::numeric(6,4)),4326)

HTH,

  Brent Wood

--- On Sat, 3/26/11, Charles Galpin <cgalpin@lhsw.com> wrote:

From: Charles Galpin <cgalpin@lhsw.com>
Subject: Re: [postgis-users] How to create a point geometry from two text fields?
To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net>
Date: Saturday, March 26, 2011, 4:34 AM

But his lat/lon are character varying so I think it would be more like
update mytable set the_geom=ST_GeometryFromText('POINT(' || long || ' ' || lat || \
')',4326)

On Mar 25, 2011, at 11:26 AM, Stephen Woodbridge wrote:
update mytable set the_geom=st_setsrid(st_makepoint(lon,lat),4326);

On 3/25/2011 10:44 AM, Gis Mage wrote:
Hello!

I have a table with two text fields "lat" and "long" of character
varying type.
I've created a field the_geom with type geometry.

How do I calculate the_geom field with an sql query?
The table is huge - about 1.5 million records, so I think the fastest
way to do this is to use pointfromtext function, but I can't figure out
how to put in the values of "lat" and "long" fields inside function
arguments.

Can anyone help me out?

Thanks.

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Attachment #5 (text/html)]

<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: \
inherit;">Concatenation of strings &amp; geomfromtext or cast to numeric &amp; \
makepoint, I'm not sure which would be faster, but both will work.<br><br>So (as \
below)&nbsp; <br><pre class="yiv385709254programlisting"><span \
class="yiv385709254Apple-style-span" style="white-space: normal;"><font \
class="yiv385709254Apple-style-span" face="Helvetica" size="3"><span \
class="yiv385709254Apple-style-span" style="font-size: 12px;">update mytable set \
the_geom=</span></font></span><font class="yiv385709254Apple-style-span" \
face="Helvetica" size="3"><span class="yiv385709254Apple-style-span" \
style="font-size: 12px;">ST_GeometryFromText('POINT(' || long || ' ' || lat || \
')',4326)<br><br>or <br><br>update mytable set \
the_geom=setsrid(makepoint(long::numeric(7,4), \
lat::numeric(6,4)),4326)<br></span></font></pre><br>HTH,<br><br>&nbsp; Brent \
Wood<br><br><br>--- On <b>Sat, 3/26/11,  Charles Galpin \
<i>&lt;cgalpin@lhsw.com&gt;</i></b> wrote:<br><blockquote style="border-left: 2px \
solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Charles \
Galpin &lt;cgalpin@lhsw.com&gt;<br>Subject: Re: [postgis-users] How to create a point \
geometry from two text fields?<br>To: "PostGIS Users Discussion" \
&lt;postgis-users@postgis.refractions.net&gt;<br>Date: Saturday, March 26, 2011, 4:34 \
AM<br><br><div id="yiv385709254">But his lat/lon are character varying so I think it \
would be more like<div><br></div><div><pre class="yiv385709254programlisting"><span \
class="yiv385709254Apple-style-span" style="white-space: normal;"><font \
class="yiv385709254Apple-style-span" face="Helvetica" size="3"><span \
class="yiv385709254Apple-style-span" style="font-size: 12px;">update mytable set \
the_geom=</span></font></span><font class="yiv385709254Apple-style-span" \
face="Helvetica" size="3"><span class="yiv385709254Apple-style-span" \
style="font-size:  12px;">ST_GeometryFromText('POINT(' || long || ' ' || lat || \
')',4326)</span></font></pre><div><br></div><div><br></div><div><div>On Mar 25, 2011, \
at 11:26 AM, Stephen Woodbridge wrote:</div><br \
class="yiv385709254Apple-interchange-newline"><blockquote type="cite"><div>update \
mytable set the_geom=st_setsrid(st_makepoint(lon,lat),4326);<br><br>On 3/25/2011 \
10:44 AM, Gis Mage wrote:<br><blockquote \
type="cite">Hello!<br></blockquote><blockquote \
type="cite"><br></blockquote><blockquote type="cite">I have a table with two text \
fields "lat" and "long" of character<br></blockquote><blockquote type="cite">varying \
type.<br></blockquote><blockquote type="cite">I've created a field the_geom with type \
geometry.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote \
type="cite">How do I calculate the_geom field with an sql \
query?<br></blockquote><blockquote type="cite">The table is huge - about 1.5 million \
records, so I think the  fastest<br></blockquote><blockquote type="cite">way to do \
this is to use pointfromtext function, but I can't figure \
out<br></blockquote><blockquote type="cite">how to put in the values of "lat" and \
"long" fields inside function<br></blockquote><blockquote \
type="cite">arguments.<br></blockquote><blockquote \
type="cite"><br></blockquote><blockquote type="cite">Can anyone help me \
out?<br></blockquote><blockquote type="cite"><br></blockquote><blockquote \
type="cite">Thanks.<br></blockquote><blockquote \
type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote \
type="cite"><br></blockquote><blockquote \
type="cite">_______________________________________________<br></blockquote><blockquote \
type="cite">postgis-users mailing list<br></blockquote><blockquote type="cite"><a \
rel="nofollow" ymailto="mailto:postgis-users@postgis.refractions.net" target="_blank" \
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br></blockquote><blockquote \
type="cite"><a rel="nofollow" target="_blank" \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.re \
fractions.net/mailman/listinfo/postgis-users</a><br></blockquote><br>_______________________________________________<br>postgis-users \
mailing list<br><a rel="nofollow" \
ymailto="mailto:postgis-users@postgis.refractions.net" target="_blank" \
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refr \
actions.net</a><br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></div></blockquote></div><br></div></div><br>-----Inline \
Attachment Follows-----<br><br><div \
class="plainMail">_______________________________________________<br>postgis-users \
mailing list<br><a ymailto="mailto:postgis-users@postgis.refractions.net"  \
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></table>

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 