[postgis-users] PostGIS to DXF output
GIS
GIS at asiaq.gl
Wed Sep 16 04:41:24 PDT 2009
Sounds great. Does Frank need funding for this work? Unfortunately our budget on these matters is used for this year, but possibly available next year.
Med venlig hilsen
Karl Brix Zinglersen
-----Oprindelig meddelelse-----
Fra: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] På vegne af postgis-users-request at postgis.refractions.net
Sendt: 15. september 2009 16:00
Til: postgis-users at postgis.refractions.net
Emne: postgis-users Digest, Vol 85, Issue 15
Send postgis-users mailing list submissions to
postgis-users at postgis.refractions.net
To subscribe or unsubscribe via the World Wide Web, visit
http://postgis.refractions.net/mailman/listinfo/postgis-users
or, via email, send a message with subject or body 'help' to
postgis-users-request at postgis.refractions.net
You can reach the person managing the list at
postgis-users-owner at postgis.refractions.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of postgis-users digest..."
Today's Topics:
1. Center of Points Collection (Dustin Butler)
2. Re: Center of Points Collection (Paul Ramsey)
3. Re: Center of Points Collection (Rick)
4. Re: Center of Points Collection (Paul Ramsey)
5. Re: Center of Points Collection (Kevin Neufeld)
6. Re: Center of Points Collection (Rick)
7. Re: Center of Points Collection (Dustin Butler)
8. No Primary Key (Ravi)
9. Re: No Primary Key (P Kishor)
10. Re: Center of Points Collection (pcreso at pcreso.com)
11. Re: multipoint2point (Richard Greenwood)
12. Pgrouting assign_vertex_id function finding lat lon values of
vertexes problem (poonam jalwan)
13. st_intersection error (D?ster Horst)
14. Re: multipoint2point (jj.wag at gmx.de)
15. Re: multipoint2point (nicklas.aven at jordogskog.no)
16. Re: st_intersection error (strk)
17. Re: st_intersection error (D?ster Horst)
18. Re: st_intersection error (strk)
19. Re: st_intersection error (nicklas.aven at jordogskog.no)
20. Re: No Primary Key (Kevin Neufeld)
21. Re: st_intersection error (Martin Davis)
22. Re: PostGIS to DXF output (Stefan Keller)
----------------------------------------------------------------------
Message: 1
Date: Mon, 14 Sep 2009 14:24:07 -0500
From: "Dustin Butler" <dustin at intrcomm.net>
Subject: [postgis-users] Center of Points Collection
To: postgis-users at postgis.refractions.net
Message-ID: <20090914192407.3740B80CAB3 at webmail.intrcomm.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Hello,
Trying to figure out how to find center using a collection of points. For example something like this which doesn't work but you get the idea.
SELECT ST_AsText(ST_Centriod(SELECT point_geom FROM pb.pb_statistics WHERE pb_debit=1));
I think I need to make a multipoint geom out of the points and pass that but haven't figure out yet.
Thanks,
Dustin Butler
Intrcomm Technology
Skype: dustinbutler
------------------------------
Message: 2
Date: Mon, 14 Sep 2009 12:28:27 -0700
From: Paul Ramsey <pramsey at cleverelephant.ca>
Subject: Re: [postgis-users] Center of Points Collection
To: Dustin Butler <dustin at intrcomm.net>, PostGIS Users Discussion
<postgis-users at postgis.refractions.net>
Message-ID:
<30fe546d0909141228t2e5801d8s766152fe7695049 at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Faster than creating a multipoint is to recognize that ST_Centroid()
is just going to return the center of the bbox of the collection
anyways, so you can replace it with.
SELECT ST_Centroid(ST_Extent(point_geom)) FROM pb_statistics WHERE pg_debit =1;
If you want to do it the hard way, then:
SELECT ST_Centroid(ST_Collect(point_geom)) FROM pb_statistics WHERE pg_debit =1;
P.
On Mon, Sep 14, 2009 at 12:24 PM, Dustin Butler <dustin at intrcomm.net> wrote:
> Hello,
>
> Trying to figure out how to find center using a collection of points. ?For
> example something like this which doesn't work but you get the idea.
>
> SELECT ST_AsText(ST_Centriod(SELECT point_geom FROM pb.pb_statistics WHERE
> pb_debit=1));
>
> I think I need to make a multipoint geom out of the points and pass that but
> haven't figure out yet.
>
> Thanks,
> Dustin Butler
> Intrcomm Technology
>
> Skype: dustinbutler
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 3
Date: Mon, 14 Sep 2009 16:03:11 -0400
From: Rick <graham.rick at gmail.com>
Subject: Re: [postgis-users] Center of Points Collection
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<18569e000909141303q297c10d9r7c133f712305236a at mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"
It occurs to me that, depending on what you want, you might try averaging
the points to get a median, since you don't have a shape, and thus no
concept of area to produce a centroid.
i.e. add them all up and divide by the number of points.
On Mon, Sep 14, 2009 at 3:28 PM, Paul Ramsey <pramsey at cleverelephant.ca>wrote:
> Faster than creating a multipoint is to recognize that ST_Centroid()
> is just going to return the center of the bbox of the collection
> anyways, so you can replace it with.
>
> SELECT ST_Centroid(ST_Extent(point_geom)) FROM pb_statistics WHERE pg_debit
> =1;
>
> If you want to do it the hard way, then:
>
> SELECT ST_Centroid(ST_Collect(point_geom)) FROM pb_statistics WHERE
> pg_debit =1;
>
> P.
>
> On Mon, Sep 14, 2009 at 12:24 PM, Dustin Butler <dustin at intrcomm.net>
> wrote:
> > Hello,
> >
> > Trying to figure out how to find center using a collection of points.
> For
> > example something like this which doesn't work but you get the idea.
> >
> > SELECT ST_AsText(ST_Centriod(SELECT point_geom FROM pb.pb_statistics
> WHERE
> > pb_debit=1));
> >
> > I think I need to make a multipoint geom out of the points and pass that
> but
> > haven't figure out yet.
> >
> > Thanks,
> > Dustin Butler
> > Intrcomm Technology
> >
> > Skype: dustinbutler
> > _______________________________________________
> > 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
>
--
Cheers!
Rick
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20090914/3a53a895/attachment-0001.html>
------------------------------
Message: 4
Date: Mon, 14 Sep 2009 13:13:26 -0700
From: Paul Ramsey <pramsey at cleverelephant.ca>
Subject: Re: [postgis-users] Center of Points Collection
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<30fe546d0909141313g680b7170jf75bc9cfbc94ed28 at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Very nice approach.
Select ST_MakePoint(avg(ST_X(point_teom)), avg(ST_Y(point_geom))) FROM
pb_statistics WHERE pg_debit =1;
On Mon, Sep 14, 2009 at 1:03 PM, Rick <graham.rick at gmail.com> wrote:
> It occurs to me that, depending on what you want, you might try averaging
> the points to get a median, since you don't have a shape, and thus no
> concept of area to produce a centroid.
>
> i.e. add them all up and divide by the number of points.
>
> On Mon, Sep 14, 2009 at 3:28 PM, Paul Ramsey <pramsey at cleverelephant.ca>
> wrote:
>>
>> Faster than creating a multipoint is to recognize that ST_Centroid()
>> is just going to return the center of the bbox of the collection
>> anyways, so you can replace it with.
>>
>> SELECT ST_Centroid(ST_Extent(point_geom)) FROM pb_statistics WHERE
>> pg_debit =1;
>>
>> If you want to do it the hard way, then:
>>
>> SELECT ST_Centroid(ST_Collect(point_geom)) FROM pb_statistics WHERE
>> pg_debit =1;
>>
>> P.
>>
>> On Mon, Sep 14, 2009 at 12:24 PM, Dustin Butler <dustin at intrcomm.net>
>> wrote:
>> > Hello,
>> >
>> > Trying to figure out how to find center using a collection of points.
>> > ?For
>> > example something like this which doesn't work but you get the idea.
>> >
>> > SELECT ST_AsText(ST_Centriod(SELECT point_geom FROM pb.pb_statistics
>> > WHERE
>> > pb_debit=1));
>> >
>> > I think I need to make a multipoint geom out of the points and pass that
>> > but
>> > haven't figure out yet.
>> >
>> > Thanks,
>> > Dustin Butler
>> > Intrcomm Technology
>> >
>> > Skype: dustinbutler
>> > _______________________________________________
>> > 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
>
>
>
> --
> Cheers!
> Rick
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
------------------------------
Message: 5
Date: Mon, 14 Sep 2009 13:51:11 -0700
From: Kevin Neufeld <kneufeld at refractions.net>
Subject: Re: [postgis-users] Center of Points Collection
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <4AAEACBF.9060209 at refractions.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Paul Ramsey wrote:
> Faster than creating a multipoint is to recognize that ST_Centroid()
> is just going to return the center of the bbox of the collection
> anyways...
Unfortunately, Paul, ST_Centroid returns the center of mass, not the center of the bbox.
SELECT astext(st_centroid(st_collect(column1))),
FROM (values ('POINT(0 0)'),
('POINT(0 1)'),
('POINT(0 2)'),
('POINT(1 0)')) as foo;
astext
------------------
POINT(0.25 0.75)
(1 row)
Your second post, taking the avg of the x,y does seem to be the nice approach, and produces the same results as
ST_Centroid - the center of mass.
SELECT astext(st_makepoint(avg(st_x(column1)), avg(st_y(column1))))
FROM (values ('POINT(0 0)'),
('POINT(0 1)'),
('POINT(0 2)'),
('POINT(1 0)')) as foo;
astext
------------------
POINT(0.25 0.75)
(1 row)
If Dustin is after the center of the collection, then something along your first suggestion might be more appropriate.
(taking the center of the extents)
Cheers,
Kevin
------------------------------
Message: 6
Date: Mon, 14 Sep 2009 18:23:57 -0400
From: Rick <graham.rick at gmail.com>
Subject: Re: [postgis-users] Center of Points Collection
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<18569e000909141523q43880f59qc2ae75b04c23e3de at mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"
This is why I said depending on what you want.
The average provides a weighted result, where a wild point will merely tug
like a point on a bezier frame. A statistical result. Putting a bounding
box around the whole thing and computing the center of that, provides an
unweighted geometric result. Which may be desirable. Without providing
some means, on a set of points that we have no description of, of ordering a
shape, save order of occurence, the means of computing a centroid is
indeterminate.
Interestingly enough, if the points are evenly spaced in circle with
ordinate points, all three will get the same result. (Completely
manufactured, I know) But I doubt that the question would have been raised
if that were the case.
The question is what is the application.
Dustin?
My question, purely out of interest, is which would be faster? My guess is
the bounding box, with the median coming close on its heels.
Also, am I correct in assuming that a centroid strives to be the point at
which, were a line drawn through it would split the area of the shape
evenly? If so, is this always possible, and if not, can exceptions be
easily identified?
On Mon, Sep 14, 2009 at 4:51 PM, Kevin Neufeld <kneufeld at refractions.net>wrote:
> Paul Ramsey wrote:
>
>> Faster than creating a multipoint is to recognize that ST_Centroid()
>> is just going to return the center of the bbox of the collection
>> anyways...
>>
>
> Unfortunately, Paul, ST_Centroid returns the center of mass, not the center
> of the bbox.
>
> SELECT astext(st_centroid(st_collect(column1))),
> FROM (values ('POINT(0 0)'),
> ('POINT(0 1)'),
> ('POINT(0 2)'),
> ('POINT(1 0)')) as foo;
> astext
> ------------------
> POINT(0.25 0.75)
> (1 row)
>
> Your second post, taking the avg of the x,y does seem to be the nice
> approach, and produces the same results as ST_Centroid - the center of mass.
>
> SELECT astext(st_makepoint(avg(st_x(column1)), avg(st_y(column1))))
> FROM (values ('POINT(0 0)'),
> ('POINT(0 1)'),
> ('POINT(0 2)'),
> ('POINT(1 0)')) as foo;
> astext
> ------------------
> POINT(0.25 0.75)
> (1 row)
>
> If Dustin is after the center of the collection, then something along your
> first suggestion might be more appropriate.
> (taking the center of the extents)
>
> Cheers,
> Kevin
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
Cheers!
Rick
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20090914/31afd580/attachment-0001.html>
------------------------------
Message: 7
Date: Mon, 14 Sep 2009 17:25:08 -0500
From: "Dustin Butler" <dustin at intrcomm.net>
Subject: Re: [postgis-users] Center of Points Collection
To: Paul Ramsey <pramsey at cleverelephant.ca>
Cc: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <20090914222508.66CF980CAEE at webmail.intrcomm.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Thanks, that worked great.
Dustin Butler
Intrcomm Technology
Skype: dustinbutler
------ Original Message ------
From: Paul Ramsey <pramsey at cleverelephant.ca>
Date: Mon, 14 Sep 2009 12:28:27 -0700
To: Dustin Butler <dustin at intrcomm.net>, PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Subject: Re: [postgis-users] Center of Points Collection
Faster than creating a multipoint is to recognize that ST_Centroid()
is just going to return the center of the bbox of the collection
anyways, so you can replace it with.
SELECT ST_Centroid(ST_Extent(point_geom)) FROM pb_statistics WHERE pg_debit =1;
If you want to do it the hard way, then:
SELECT ST_Centroid(ST_Collect(point_geom)) FROM pb_statistics WHERE pg_debit =1;
P.
On Mon, Sep 14, 2009 at 12:24 PM, Dustin Butler <dustin at intrcomm.net> wrote:
> Hello,
>
> Trying to figure out how to find center using a collection of points. ?For
> example something like this which doesn't work but you get the idea.
>
> SELECT ST_AsText(ST_Centriod(SELECT point_geom FROM pb.pb_statistics WHERE
> pb_debit=1));
>
> I think I need to make a multipoint geom out of the points and pass that but
> haven't figure out yet.
>
> Thanks,
> Dustin Butler
> Intrcomm Technology
>
> Skype: dustinbutler
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 8
Date: Mon, 14 Sep 2009 15:34:28 -0700 (PDT)
From: Ravi <ravivundavalli at yahoo.com>
Subject: [postgis-users] No Primary Key
To: postgis <postgis-users at postgis.refractions.net>
Message-ID: <674439.74509.qm at web65609.mail.ac4.yahoo.com>
Content-Type: text/plain; charset=utf-8
Hi,
have a problem with a query resulting in a table without a primary key.
Pl suggest a proper SQL
Thanks in anticipation
Ravi
create table coal AS SELECT * from geology where group_='GONDWANA';