Re: [postgis-users] PostGIS Raster for Met Data

[Michael Akinde]

Hi,

Thanks for the suggestions. I'm going to try a few experiments with our code, 
and probably get back to this later.

Regards,

Michael A.

- Original Message -
> > - Original Message -
> > > For sure doing overlay gis operations like st_intersection is not
> > > possible when the srid of the two geometries is different. In this
> > > case you would have to reproject (ST_Transform) the geometry so it
> > > fit
> > > the srid of right grid. But if your application is able to decide
> > > which grid it must query then it should also be able to decide
> > > which
> > > tale it must query and hence putting all your grids in different
> > > table
> > > should not be a problem.
> >
> > For the general case (particularly in the coast example, where the
> > queries are
> > basically just Lon/Lat), we would not know which grid contains the
> > answer.
> > Consequently we would have to do two queries (one to determine the
> > grid to
> > query and one to query the specific grid), where today we make do
> > with one. I
> > suspect that this would cost us a fair bit of performance.
> 
> You could have a table of the outlines of each dataset and use CASE to
> determine to which srid reproject your point. All that in one query.
> 
> > > You can easily get the x,y upperleft corner of a pixel with the
> > > Raster2WorldcoordX(0,0) and Raster2WorldcoordY(1,0) functions. Is
> > > that
> > > what you need?
> >
> > Not quite - I was thinking more of World2RasterCoord, but using a
> > geometry to
> > extract multiple raster points. Essentially, ST_INTERSECT, but
> > returning raster
> > coordinates rather than world coordinates and value.
> 
> You can extract every point of a complex geometry using a mixture of
> ST_PointN(),ST_NPoints(),ST_NumGeometries() and ST_GeometryN(). Have a
> look in the PostGIS doc.
> 
> Pierre
> ___
> 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

Re: [postgis-users] wrong ST_Polygonize

[karpi . lists]

Hello Ralf,
thank you again for your response.
As you note, the endpoints really differs:
01012011087B14AE87F50125C18FC2F5685B2C30C1
01012011087814AE87F50125C184C2F5685B2C30C1
(on byte 20) .. i've missed this..

St_shortestLine(), or manual snap in qgis will propably be solution ,)


pk




>
>Hello,
>
>your last two linestrings did not intersect.
>st_snaptogrid(geom, 0.01) can fix it or to preserve topologie, you can
>close the gap with a linestring from st_shortestLine().
>
>Gr
>Ralf
>

karpi prijmeni
karpi.li...@email.cz
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_ConcaveHull

[Ahmad Aburizaiza]

Try this.

Select ST_ConcaveHull(ST_Collect(the_geom)) FROM table;


*---
Ahmad Aburizaiza
PhD student at **George Mason University**
Geography and Geoinformation Science Department
Fairfax, VA, USA
Tel : +1-703-981-0354*



On Thu, Jul 7, 2011 at 10:44 AM, Brian Sanjeewa Rupasinghe <
jink...@gmail.com> wrote:

> Hi,
>
> I have a building polygon table and from the following query i get the
> polygon
> coordinates.
>
> select st_astext (st_geometryn(the_geom, 1))
> from building_poly;
>
> Now what i need is to get the concave hull of all polygon coordinates.
> I tried the following query but it does not work.
>
> SELECT ST_ConcaveHull(ST_Union(select st_astext (st_geometryn(the_geom,
> 1))
> from building_poly), 1)  As convexhull;
>
> How to correct it?
>
> Cheers, Sanjeewa.
>
> ___
> 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

Re: [postgis-users] Issue with ST_Line_Interpolate_Point dropping m values

[Craig McIlwee]

Well I think Ive found the issue.  The function handles LINESTRING and 
MULTILINESTRING just fine, but gives 0 M values for LINESTRINGM and 
MULTILINESTRINGM.
 
SELECT ST_AsEWKT(ST_Line_Interpolate_Point(the_line, 0.5))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 4 5 6, 6 7 8)') 
as the_line) As foo;
"POINTM(3.5 4.5 0)"
 
Is this expected behavior?  For now I think I can work around it by pulling the 
EWKT over to my client, doing a string replace on MULTILINESTRINGM to 
MULTILINESTRING, then using ST_GeomFromEWKT in a subsequent query.  It will 
certainly complicate my code and introduce a bit of I/O overhead, but it seems 
to work.
 
Craig
 
From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Craig 
McIlwee
Sent: Tuesday, July 12, 2011 2:07 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] Issue with ST_Line_Interpolate_Point dropping m values
 
Hello,
 
Im using PostGIS 1.5 and am having trouble with the ST_Line_Interpolate_Point 
function, the M value of the returned points are always 0.   An example query 
with output is:
 
select st_asewkt(st_line_interpolate_point(st_geometryn(the_geom, 1), 
0.88364273)) from wgs_statecov_tway;
"SRID=4326;POINTM(-90.4466123484958 38.8893086051018 0)"
 
 
My geometries are MULTILINESTRINGM type, and examination with ST_AsEWKT shows 
proper M values as you see below.  I also use the M value for other LRS 
functions.
 
select st_asewkt(st_endpoint(the_geom)) from wgs_statecov_tway;
"SRID=4326;POINTM(-90.2232337557835 38.8615691007846 136.351980792317)"
 
 
The odd thing is that when I try the example query from the reference manual 
everything works fine.
 
SELECT ST_AsEWKT(ST_Line_Interpolate_Point(the_line, 0.5))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 4 5 6, 6 7 8)') 
as the_line) As foo;
"POINT(3.5 4.5 5.5)"
 
 
Based on this, it seems like I must be doing something but because the query is 
so simple I have no idea what that could be.  Any ideas?
 
Thanks,
Craig
Open Roads Consulting, Inc.
757-546-3401
http://www.openroadsconsulting.com
 
This e-mail communication (including any attachments) may contain confidential 
and/or privileged material intended solely for the individual or entity to 
which it is addressed.  If you are not the intended recipient, you should 
immediately stop reading this message and delete it from all computers that it 
resides on. Any unauthorized reading, distribution, copying or other use of 
this communication (or its attachments) is strictly prohibited.  If you have 
received this communication in error, please notify us immediately.
 


This e-mail communication (including any attachments) may contain confidential 
and/or privileged material intended solely for the individual or entity to 
which it is addressed.
P - Think before you print.

This e-mail communication (including any attachments) may contain confidential 
and/or privileged material intended solely for the individual or entity to 
which it is addressed.
P - Think before you print.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Issue with ST_Line_Interpolate_Point dropping m values

[Craig McIlwee]

Hello,
 
Im using PostGIS 1.5 and am having trouble with the ST_Line_Interpolate_Point 
function, the M value of the returned points are always 0.   An example query 
with output is:
 
select st_asewkt(st_line_interpolate_point(st_geometryn(the_geom, 1), 
0.88364273)) from wgs_statecov_tway;
"SRID=4326;POINTM(-90.4466123484958 38.8893086051018 0)"
 
 
My geometries are MULTILINESTRINGM type, and examination with ST_AsEWKT shows 
proper M values as you see below.  I also use the M value for other LRS 
functions.
 
select st_asewkt(st_endpoint(the_geom)) from wgs_statecov_tway;
"SRID=4326;POINTM(-90.2232337557835 38.8615691007846 136.351980792317)"
 
 
The odd thing is that when I try the example query from the reference manual 
everything works fine.
 
SELECT ST_AsEWKT(ST_Line_Interpolate_Point(the_line, 0.5))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 4 5 6, 6 7 8)') 
as the_line) As foo;
"POINT(3.5 4.5 5.5)"
 
 
Based on this, it seems like I must be doing something but because the query is 
so simple I have no idea what that could be.  Any ideas?
 
Thanks,
Craig
Open Roads Consulting, Inc.
757-546-3401
http://www.openroadsconsulting.com
 
This e-mail communication (including any attachments) may contain confidential 
and/or privileged material intended solely for the individual or entity to 
which it is addressed.  If you are not the intended recipient, you should 
immediately stop reading this message and delete it from all computers that it 
resides on. Any unauthorized reading, distribution, copying or other use of 
this communication (or its attachments) is strictly prohibited.  If you have 
received this communication in error, please notify us immediately.
 

This e-mail communication (including any attachments) may contain confidential 
and/or privileged material intended solely for the individual or entity to 
which it is addressed.
P - Think before you print.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] topology point - linestring

[matze09]

Stephen, Nicolas wrote:
> 
>>
>> Hi Matthias,
>>
>> Maybe st_linecrossingdirection could be used ?
>> (http://postgis.refractions.net/documentation/manual-1.5/ST_LineCrossingDirection.html)
>> using a tiny linestring composed of one point and a slight translation
>> of this point ?
> 
> 
> How about st_shortestline [1] this gives you a vector from the point to 
> the line, now you can get a vector along you reference line from start 
> to that point or that point to the end of the the reference line. If you 
> compute the cross product of those to vectors and the sign of the Z 
> value of the cross product is positive you point is to the right, 
> negative to the left and zero means you are on the line.
> 
> I think I would probably wrap this into a stored procedure that returns 
> a record like distance|side|point_on_line or something like that.
> 
> -Steve
> 
> [1] 
> http://postgis.refractions.net/documentation/manual-1.5/ST_ShortestLine.html
> 
> 

hi nicolas, hi steve,

thanks a lot for your quick replies. i stumbled over the
st_linecrossingdirection function before but thought
it wouldn't be the most efficient way. well, it certainly isn't but at least
it seems to work.
i included the st_closestpoint function since it might be faster than
st_line_locate_point and st_line_interpolate_point.

the function below takes a point and a linestring as arguments and returns
-1/0/1 according to the topology. it works fine except for points where the
closest point coincides with the 
line's start/endpoint (in that case, the result is always 0 because there is
no intersection.)
will try to fix that later.

for now thanks and all the best,
matthias


CREATE FUNCTION pointToLinePos(geometry,geometry) RETURNS integer
AS 'SELECT 
(-1)*st_linecrossingdirection(st_makeline($1,st_translate(foo.pt,0.1*st_x(foo.pt)-st_x($1)),0.1*st_y(foo.pt)-st_y($1,
$2)
FROM ( SELECT st_closestpoint($2,$1) AS pt ) AS foo;'
LANGUAGE sql
IMMUTABLE
RETURNS null on null input;

-- 
View this message in context: 
http://old.nabble.com/topology-point---linestring-tp32045866p32047820.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

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

Re: [postgis-users] converting to lat long

[pcreso]

Instead of storing lat & long columns of numbers, create a point geometry with 
Postgis, you can still select the lat & long values as shown below.

select addgeometrycolumn('','tablename','geom',4326,'POINT',2);

To run this change 'tablename' to the name of the table with the geocode 
column. This will give you a Postgis geometry column to store lat/long points 
in.

geocode is presumeably a string datatype, and there are a number of ways to 
parse this, here is one example. I'm assuming each value will always be 4 
digits then "/" then 4 digits, as in your example. If not, then you'll need 
another approach.

update tablename
set geom=setsrid(makepoint(substr(geocode,6,4)::decimal(6,2)/100,
   substr(geocode,1,4)::decimal(6,2)/100),
 4326);

This takes characters 6-9 of the geocode value, converts to a number, divides 
by 100, then does the same to characters 1-4, which generates your lon & lat 
(in xy order) values as you describe them, then assigns a srid of 4326 to tell 
postgis the coordinates are indeed lat/long ones, and sets the value of each 
point geometry to these coordinates.

If you want to see the resulting lat/long values now stored in the geom column, 
try:

select geocode, astext(geom) from tablename;
or
select geocode, y(geom) as lat, x(geom) as lon from tablename; 


HTH,

  Brent Wood

--- On Wed, 7/13/11, Yamini Singh  wrote:

From: Yamini Singh 
Subject: [postgis-users] converting to lat long
To: "PostGIS User List" 
Date: Wednesday, July 13, 2011, 12:58 AM





Hi All,


I have a column 'geocode' in a table which has
attributes like 2329/4727 now I would like to convert these attributes in 
another
column to 'lat' and 'long'. For example, 'Lat' column will 
have attribute '23.29' and 'long' column will have attribute  '47.27'.

Is there a way through which this can be
converted automatically by a query or so.
Looking fwd..

   

Thanks 

YJ 

-Inline Attachment Follows-

___
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

Re: [postgis-users] topology point - linestring

[Stephen Woodbridge]

On 7/12/2011 10:57 AM, Nicolas Ribot wrote:

hi all,

within a linear referencing task i need the shortest distances of all points
to the linestring.
that itself wouldn't be a problem with st_line_locate_point and st_distance,
but i also need to know the topological position of the point relative to
the linestring (left, right or on the linestring).

e.g. the linear referencing tool in ArcGIS indicates the relative location
of a point to a line by means of a (un-)signed distance value (the tool
returns positive and negative distance values).

i haven't found any postgis function yet for this problem.
does anyone know of a function which solves this? or a workaround which runs
as quick as possible?

thanks in advance and
best regards,

matthias


Hi Matthias,

Maybe st_linecrossingdirection could be used ?
(http://postgis.refractions.net/documentation/manual-1.5/ST_LineCrossingDirection.html)
using a tiny linestring composed of one point and a slight translation
of this point ?



How about st_shortestline [1] this gives you a vector from the point to 
the line, now you can get a vector along you reference line from start 
to that point or that point to the end of the the reference line. If you 
compute the cross product of those to vectors and the sign of the Z 
value of the cross product is positive you point is to the right, 
negative to the left and zero means you are on the line.


I think I would probably wrap this into a stored procedure that returns 
a record like distance|side|point_on_line or something like that.


-Steve

[1] 
http://postgis.refractions.net/documentation/manual-1.5/ST_ShortestLine.html


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

Re: [postgis-users] topology point - linestring

[Nicolas Ribot]

> hi all,
>
> within a linear referencing task i need the shortest distances of all points
> to the linestring.
> that itself wouldn't be a problem with st_line_locate_point and st_distance,
> but i also need to know the topological position of the point relative to
> the linestring (left, right or on the linestring).
>
> e.g. the linear referencing tool in ArcGIS indicates the relative location
> of a point to a line by means of a (un-)signed distance value (the tool
> returns positive and negative distance values).
>
> i haven't found any postgis function yet for this problem.
> does anyone know of a function which solves this? or a workaround which runs
> as quick as possible?
>
> thanks in advance and
> best regards,
>
> matthias

Hi Matthias,

Maybe st_linecrossingdirection could be used ?
(http://postgis.refractions.net/documentation/manual-1.5/ST_LineCrossingDirection.html)
using a tiny linestring composed of one point and a slight translation
of this point ?

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

Re: [postgis-users] converting to lat long

[Ben Madin]

FWIW - PostGIS uses long lat format...

If you are confident about the data consistency, this should be a pretty 
straightforward task - if you are not sure, it might still be quite simple if 
you are prepared to look into the mystical world of regular expressions. 
PostgreSQL offers support for these on the previously recommended string 
expressions page, but you will also need to look at the pattern matching page.

If you haven't ever looked at regular expressions, there are a number of very 
good resources available on the web. They are annoyingly useful, so worth 
investing some time in learning about them.

cheers

Ben


On 12/07/2011, at 8:58 PM, Yamini Singh wrote:

> Hi All,
> 
> I have a column 'geocode' in a table which has attributes like 2329/4727 now 
> I would like to convert these attributes in another column to 'lat' and 
> 'long'. For example, 'Lat' column will have attribute '23.29' and 'long' 
> column will have attribute  '47.27'.
> Is there a way through which this can be converted automatically by a query 
> or so.
> 
> Looking fwd..
>  
> Thanks
> YJ
> ___
> 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

Re: [postgis-users] wrong ST_Polygonize

[Ralf Suhr]

Hello,

your last two linestrings did not intersect. 
st_snaptogrid(geom, 0.01) can fix it or to preserve topologie, you can 
close the gap with a linestring from st_shortestLine().

Gr
Ralf

Am Dienstag 12 Juli 2011, 14:22:02 schrieb karpi.li...@email.cz:
> Hello,
> i was falled into troubles with ST_Polygonize, there is example:
> 
> three linestrings:
> 010220110802002C35CC432E0325C1E84311A7272930C12B79E346610325C1E
> E78E89A372930C1
> 
> 01022011081500CBA455B5270325C1798A4C671D2930C10C37E255270325C1D
> DFC01F41D2930C115767D09270325C1D3AA98831E2930C125297FD0260325C19E976B151F29
> 30C1E6CC28AB260325C1F734D3A81F2930C12B48A599260325C19C23263D202930C1A3BA089
> C260325C1E3F5B9D1202930C1BC6550B2260325C195F3E365212930C1CAAF62DC260325C117
> DEF9F8212930C171410F1A270325C103B4528A222930C12F3D0F6B270325C15C73471923293
> 0C1CD9005CF270325C175D933A5232930C14B607F45280325C1AD1F772D242930C1DD89F4CD
> 280325C129B474B1242930C15842C867290325C1BEED9430252930C157C949122A0325C136B
> A45AA252930C15534B5CC2A0325C12246FB1D262930C1CB4F34962B0325C1879D308B262930
> C14C95DF6D2C0325C1A34468F1262930C18F35BF522D0325C119C82C50272930C12C35CC432
> E0325C1E84311A7272930C1
> 
> 01022011080200F2E13C88810325C1158B099BD32830C1CBA455B5270325C17
> 68A4C671D2930C1
> 
> They are touched in their end points:
> 0101201108CBA455B5270325C1798A4C671D2930C1
> 01012011082C35CC432E0325C1E84311A7272930C1
> 
> ST_Touches is correct
> true
> ST_Relate is correct:
> FF1F00102
> 
> So.. why they are not polygonized together ? Why is polygonized only the
> multipointed linestring without two short adjacent lines? Can be this
> workarounded in some way (but if I want to avoid to change entities by
> snapping) ?
> 
>
>  thanks for response, pk
> 
> 
> 
> 
> 
> 
> 
> ___
> 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

[postgis-users] topology point - linestring

[matze09]

hi all,

within a linear referencing task i need the shortest distances of all points
to the linestring.
that itself wouldn't be a problem with st_line_locate_point and st_distance,
but i also need to know the topological position of the point relative to
the linestring (left, right or on the linestring).

e.g. the linear referencing tool in ArcGIS indicates the relative location
of a point to a line by means of a (un-)signed distance value (the tool
returns positive and negative distance values).

i haven't found any postgis function yet for this problem.
does anyone know of a function which solves this? or a workaround which runs
as quick as possible?

thanks in advance and
best regards,

matthias
-- 
View this message in context: 
http://old.nabble.com/topology-point---linestring-tp32045866p32045866.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

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

Re: [postgis-users] converting to lat long

[MarkW]

Just to add to the other good advice you've already received:

If the data are in the format with an actual slash, as a single string (not
sure from your email), then string functions can help you get to the point
where you can divide, or create geometry, if that's where you're at:
http://www.postgresql.org/docs/8.4/interactive/functions-string.html

Mark

On Tue, Jul 12, 2011 at 8:58 AM, Yamini Singh  wrote:

>  *Hi All,*
>
> *I have a column 'geocode' in a table which has attributes like 2329/4727
> now I would like to convert these **attributes **in another column to
> 'lat' and 'long'. For example, 'Lat' column will have **attribute '**23.29'
> and 'long' column will have **attribute ** '47.27'.*
>
> *Is there a way through which this can be converted automatically by a
> query or so.*
>
> *Looking fwd..Thanks*
>
> *YJ*
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] converting to lat long

[Gheorghiu, Mihai]

I would suggest that first you check your data has a consistent format, i.e. 
"zero" is represented as , 12.3 degrees is represented as 1230 and 1.23 
degrees is represented as 0123.
Otherwise you need to do a little preparation work...

HTH,

Michael

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of John 
Callahan
Sent: Tuesday, July 12, 2011 9:13 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] converting to lat long

Looks like you already have lat/long coordinates and don't need to use 
ST_Transform.  Make sure you know if the original values are in 'decimal 
degrees' or 'degrees minutes seconds'.  If in DD, just divide your values by 
100 which should be easy in SQL.  If in DMS, then you need to parse the data 
first.  I'm not sure how to do that in SQL but sure it's possible.

- John


On Tue, Jul 12, 2011 at 8:58 AM, Yamini Singh 
mailto:yaminijsi...@live.com>> wrote:
Hi All,

I have a column 'geocode' in a table which has attributes like 2329/4727 now I 
would like to convert these attributes in another column to 'lat' and 'long'. 
For example, 'Lat' column will have attribute '23.29' and 'long' column will 
have attribute  '47.27'.
Is there a way through which this can be converted automatically by a query or 
so.

Looking fwd..

Thanks
YJ

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



The information contained in this transmission is to be considered CONFIDENTIAL 
and PROPRIETARY to Consortium Health Plans, Inc. and intended for the use of 
the Individual or Entity named above.  If the reader of this message is not the 
Intended Recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is Strictly Prohibited.  If you 
have received this transmission in error, please notify us immediately by 
telephone at 410-772-2900 or return email to sender immediately.  Thank You.___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] converting to lat long

[James David Smith]

YJ,

I'm a beginner, so take with a pinch of salt, but first I'd question
why you want/need to separate the latitude & longitude?

I would keep them together and store them as a point in a new column.
First create a column to store the Lat & Long in with something like
this:

SYNTAX:SELECT AddGeometryColumn(, ,
, , )
EXAMPLE:  SELECT AddGeometryColumn('parks', 'park_geom','4326', 'POINT', 2 );

Then you want to populate that column, with the existing data from
your geocode column. Using something like this:

UPDATE tablename
SET latlongcolumn = ST_GeomFromText('geocode', 4326)

I guess that there is a way to pull out the lat and long into seperate
columns, but I don't know how to do that I'm afraid.

Cheers

James





On 12 July 2011 13:58, Yamini Singh  wrote:
> Hi All,
>
> I have a column 'geocode' in a table which has attributes like 2329/4727 now
> I would like to convert these attributes in another column to 'lat' and
> 'long'. For example, 'Lat' column will have attribute '23.29' and 'long'
> column will have attribute  '47.27'.
>
> Is there a way through which this can be converted automatically by a query
> or so.
>
> Looking fwd..
>
>
>
> Thanks
>
> YJ
>
> ___
> 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

Re: [postgis-users] converting to lat long

[John Callahan]

Looks like you already have lat/long coordinates and don't need to use
ST_Transform.  Make sure you know if the original values are in 'decimal
degrees' or 'degrees minutes seconds'.  If in DD, just divide your values by
100 which should be easy in SQL.  If in DMS, then you need to parse the data
first.  I'm not sure how to do that in SQL but sure it's possible.

- John



On Tue, Jul 12, 2011 at 8:58 AM, Yamini Singh  wrote:

>  *Hi All,*
>
> *
> *
>
> *I have a column 'geocode' in a table which has attributes like 2329/4727
> now I would like to convert these **attributes **in another column to
> 'lat' and 'long'. For example, 'Lat' column will have **attribute '**23.29'
> and 'long' column will have **attribute ** '47.27'.*
>
> *Is there a way through which this can be converted automatically by a
> query or so.*
>
> *
> *
>
> *Looking fwd..*
>
> * *
>
> *Thanks*
>
> *YJ*
>
> ___
> 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

[postgis-users] converting to lat long

[Yamini Singh]

Hi All,


I have a column 'geocode' in a table which has
attributes like 2329/4727 now I would like to convert these attributes in 
another
column to 'lat' and 'long'. For example, 'Lat' column will have attribute 
'23.29' and 'long' column will have attribute  '47.27'.

Is there a way through which this can be
converted automatically by a query or so.
Looking fwd..

 

Thanks

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

[postgis-users] wrong ST_Polygonize

[karpi . lists]

Hello,
i was falled into troubles with ST_Polygonize, there is example:

three linestrings:
010220110802002C35CC432E0325C1E84311A7272930C12B79E346610325C1EE78E89A372930C1

01022011081500CBA455B5270325C1798A4C671D2930C10C37E255270325C1DDFC01F41D2930C115767D09270325C1D3AA98831E2930C125297FD0260325C19E976B151F2930C1E6CC28AB260325C1F734D3A81F2930C12B48A599260325C19C23263D202930C1A3BA089C260325C1E3F5B9D1202930C1BC6550B2260325C195F3E365212930C1CAAF62DC260325C117DEF9F8212930C171410F1A270325C103B4528A222930C12F3D0F6B270325C15C734719232930C1CD9005CF270325C175D933A5232930C14B607F45280325C1AD1F772D242930C1DD89F4CD280325C129B474B1242930C15842C867290325C1BEED9430252930C157C949122A0325C136BA45AA252930C15534B5CC2A0325C12246FB1D262930C1CB4F34962B0325C1879D308B262930C14C95DF6D2C0325C1A34468F1262930C18F35BF522D0325C119C82C50272930C12C35CC432E0325C1E84311A7272930C1

01022011080200F2E13C88810325C1158B099BD32830C1CBA455B5270325C1768A4C671D2930C1

They are touched in their end points:
0101201108CBA455B5270325C1798A4C671D2930C1
01012011082C35CC432E0325C1E84311A7272930C1

ST_Touches is correct
true
ST_Relate is correct:
FF1F00102

So.. why they are not polygonized together ? Why is polygonized only the 
multipointed linestring without two short adjacent lines?
Can be this workarounded in some way (but if I want to avoid to change entities 
by snapping) ?


 thanks for response, pk







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

Re: [postgis-users] Newbie - Spatial Join with a large dataset

[Nicolas Ribot]

> Rich,
>
> If you've got those details then you should be good to go. Use the
> ST_Within Function to select the points that are within the polygons
> of the contients that you want to show.
>

Hi,

If you have a table listing the continent of each country, you could
use it to split your data without using a spatial operator. It may be
faster.

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

[postgis-users] Extras Tiger Geocoder: additional indexes for improved performance?

[Daniel Weitzenfeld]

Are there indexes I can add to the Tiger database to make the Geocoder run
faster?
I saw this thread, but I wasn't sure what the takeaway was:
http://postgis.refractions.net/pipermail/postgis-users/2011-May/029737.html
I'm getting good speeds - 330 ms avg. on a challenging test set - but I'm
wondering if there are 'easy' ways to make it even faster.

Thanks!
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users