[postgis] Adding PostGIS data to a table.

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

[postgis] Adding PostGIS data to a table.

ted-3
I have latitude, longitude, and (sometimes) altitude information in my
database.  How do I convert/add these data as a geometry column?
The intended use is to compute distances between points, or the number
of places with in a certain distance of another point.
I'd be adding them as a POINT.

Is PostGIS the proper tool?


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

Paul Ramsey-2
[hidden email] wrote:
>
> I have latitude, longitude, and (sometimes) altitude information in my
> database.  How do I convert/add these data as a geometry column?

Converting them in situ might actually be a little tricky, as we do not
have a constructure function for points which works on floats -- the
constructors are for OGC well-known text. Eg: 'POINT(23 23 23)' in your
case. Dumping the points to a text file and then reconstructing them as
POINT text would be one straightforward means. If you have your points
in ESRI Shape format, the shape->postgis data loader is now part of the
0.5 code bundle.

> The intended use is to compute distances between points, or the number
> of places with in a certain distance of another point.
> I'd be adding them as a POINT.
> Is PostGIS the proper tool?

As your quantity of data grows, you'll find that the PostGIS spatial
indexing massively outperforms the alternatives. If you have small data
volumes, maybe not. The point_inside_circle function requested by Doug
Nebert and included in 0.5 handles one of your requirements. The
distance function is not currectly implemented (because proper
implementation is so hard for things like polygons etc). However, we
could implement a form of the distance function which does the right
thing for points and trivial simplifications for the harder cases.

Paul

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

ted-3
Then am I limited to a granularity of 1 degree?

> constructors are for OGC well-known text. Eg: 'POINT(23 23 23)' in your


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

Paul Ramsey-2
Oh, definately not! Sorry for the simplistic example... all coordinates are double-precision floats, sufficient for good storage of lat/lon.
Regarding help w/ the doco, we can arrange CVS access and you can work on the source xml doc if you wish....
Paul

Ted Rolle <[hidden email]> said:

> Then am I limited to a granularity of 1 degree?
>
> > constructors are for OGC well-known text. Eg: 'POINT(23 23 23)' in your
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>  
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 
>
>
>



--
  Paul Ramsey
  [hidden email]
  Refractions Research Inc
  (250) 885-0632

 

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

ted-3
OK on the CVS access...

I'd like to see an example of something like what I'm trying to do -- even
if I have to create it.  I suppose that many potential users have this
type of data, probably because it's the most familiar description of a
point on the earth's surface.  Something in the way of familiar examples
would do much for a new user -- and make it immediately useful.

Please be patient -- I'll get it eventually... ;->

Ted


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

Paul Ramsey-2
Ted Rolle wrote:

> I'd like to see an example of something like what I'm trying to do -- even
> if I have to create it.  I suppose that many potential users have this
> type of data, probably because it's the most familiar description of a
> point on the earth's surface.  Something in the way of familiar examples
> would do much for a new user -- and make it immediately useful.
>
> Please be patient -- I'll get it eventually... ;->

OK, first of all from a pure SQL point of view...

1) compile install the PostGIS extensions, load the postgis.sql file
into
the database your are using

2) create the table you will be using

create table mypoints ( pt geometry, name varchar(35), size int4 );

3) insert some data into the table

insert into mypoints ( pt, name, size ) values ( 'POINT(-126.14111
45.123141 87.14)', 'Location A', 14 );
insert into mypoints ( pt, name, size ) values ( 'POINT(-125.31411
45.98142 24.41)', 'Location B', 23 );

4) pull some data out of the table

select * from mypoints where pt && 'BOX3D(-127.0 45.0,-126.0
46.0)'::box3d;

Secondly from a ESRI Shapefile point of view, using the new shp2pgsql
tool:

1) compile the shp2pgsql tool (enter directory and 'make').

2) get a shape file

3) shp2pgsql <shpfile> <tablename> | psql -d dbname

What would be a good way to do this for people? Perhaps some bundled
data and a set of tutorial steps on loading, builing indexes, querying,
etc?

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Secure your servers with 128-bit SSL encryption! Grab your copy of
VeriSign's FREE Guide "Securing Your Web Site for Business." Get it now!
http://www.verisign.com/cgi-bin/go.cgi?a=n094442340008000
http://us.click.yahoo.com/6lIgYB/IWxCAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

RE: [postgis] Adding PostGIS data to a table.

Norman Vine
Paul Ramsey writes:
>
>1) compile the shp2pgsql tool (enter directory and 'make').

FWIW
I needed to change the (3) comment lines

  //the previous char isnt a \

to

 //the previous char isnt a '\'

as the naked backslash somehow was confusing Cygwin's gcc

Cheers

Norman Vine


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

ted-3
In reply to this post by Paul Ramsey-2
Well, I did what you said and it worked... except I don't have a clue as
to what an ESRI shape file is... Time, patience, and perseverance will
accomplish all things!

I'll be happy to "newbiefy" these directions and put them into the CVS,
perhaps in a HOW-TO section?  Or following the install instructions?
whatever....

Ted


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Secure your servers with 128-bit SSL encryption! Grab your copy of
VeriSign's FREE Guide "Securing Your Web Site for Business." Get it now!
http://www.verisign.com/cgi-bin/go.cgi?a=n094442340008000
http://us.click.yahoo.com/6lIgYB/IWxCAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

ted-3
In reply to this post by Paul Ramsey-2
How do I make a LINESTRING from two POINTs?  What I want to do is get the
{length2d|length3d} of this LINESTRING to determine the distance between
two points.

Ted


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

Paul Ramsey-2
The $60000 question: at the moment, you cannot do so within the
database. I think a better bet would be for us to get a
distance(<geometry>,<geometry>) function in there ASAP. What say you,
Dave?

Ted Rolle wrote:
>
> How do I make a LINESTRING from two POINTs?  What I want to do is get the
> {length2d|length3d} of this LINESTRING to determine the distance between
> two points.
>
> Ted

--
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: [hidden email]
     | Phone: (250) 885-0632
     \_

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Adding PostGIS data to a table.

Paul Ramsey-2
In reply to this post by Norman Vine
Thanks Norman, fix applied.
P.

Norman Vine wrote:

>
> Paul Ramsey writes:
> >
> >1) compile the shp2pgsql tool (enter directory and 'make').
>
> FWIW
> I needed to change the (3) comment lines
>
>   //the previous char isnt a \
>
> to
>
>  //the previous char isnt a '\'
>
> as the naked backslash somehow was confusing Cygwin's gcc
>
> Cheers
>
> Norman Vine
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

--
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: [hidden email]
     | Phone: (250) 885-0632
     \_

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/