[postgis-users] Fwd: re:I want to write "cluster by" instead of "group by" on geospatial point
hi,Kevin,thanks for your help.
Now, I'll explain my initial motivation.
Suppose we have a table with:
create table houses (
NAME VARCHAR(128) not null,
);
SELECT AddGeometryColumn('houses', 'location', 4214, 'POINT', 2);
if we want to do clustering(in data mining environment) the houses on
location, that is, clustering those house near as a cluster(or a group), and
then calculate each cluster's shape, we can not use the following sql:
select ST_Boundary(*)
from houses
group by location
I would like to extend the postgresql or postgis to support the following
sql:
select ST_Boundary(*)
from houses
clustering by location
in which the following steps are considered:
1. cluster the houses into several goups
2. calculate each goup's shape
I know the second step is usually related to aggregation functions like
sum,count,and so on which i don't care too much now. All what I want to do
is to add this sql semantics into postgresql or postgis and then add a
clustering algorithm like dbscan in the executor in postgresql(or postgis?).
How could I do? Any detailed steps?Like I should modify kwlist.h to support
"CLUSTERING" keyword, and the following steps?
Thanks!
peng
---
we all know
I'm not sure I follow. Can you explain what exactly you want to do?
The following query will collect points into clusters (multipoints),
clustered on a 100x100 grid.
-- generate a sample random point dataset
CREATE TABLE points AS
SELECT ST_MakePoint(random()*1000, random()*1000) AS geom
FROM generate_series(1, 10);
-- create point clusters
SELECT st_collect(geom)
FROM points
GROUP BY
round(st_x(geom)/100)*100,
round(st_y(geom)/100)*100;
Kevin
On 2/4/2010 11:26 PM, sunpeng wrote:
> I want to write "cluster by" instead of "group by" on geospatial point
> data,should I write the code at postgresql or postgis ?
> thanks
>
> peng
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RFQ: WKT Raster SQL API for being able to intersect vector and raster tables
Hi PostGIS folks, Now that you have a little bit of time after the release of PostGIS 1.5, I would like to request your comments on the WKT Raster specifications drawn to meet objective 0.1.6 - "Being able to intersect vector and raster to produce vector". In other word: what set of function do we need to be able to intersect a geometry layer with a raster layer? You will find the last version of the written specs here: http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationWorking01#Objective0.1.6e-Beingabletointersectvectorandrastertoproducevector This kind of operation is a generalisation of the operation needed by many people doing spatial analysis involving raster and vector. Some examples of users expressing their needs for a similar functionality: http://postgis.refractions.net/pipermail/postgis-users/2009-April/023218.html http://postgis.refractions.net/pipermail/postgis-users/2009-March/022890.html Some from the r-sig-geo group: https://stat.ethz.ch/pipermail/r-sig-geo/2009-December/007177.html https://stat.ethz.ch/pipermail/r-sig-geo/2009-November/006999.html We plan on reusing as much as possible existing PostGIS functions. The basic idea is to convert (only the) WKT Raster tiles involved in the intersect to geometries and then to procede to a normal vector intersection using the PostGIS intersection function. It involves functions like: ST_Envelope(raster), ST_ConvexHull(raster), ST_Shape(raster, integer), ST_AsPolygon(raster, integer), ST_AsWKTPolygon(raster, integer), ST_Intersects(raster, integer, geometry), ST_Intersection(raster, integer, geometry). Jorge is in charge of the implementation. We are now in the process of testing if it is worth using the polygonize function of GDAL, thus linking with GDAL, or implementing our own. A summany of the pros and cons is available at this location: http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationWorking01#TheprosandconsofusingGDALvsimplementingourownrasterservices We are also discussing the pros and cons of directly returning PostGIS geometry objects, thus having to link with PostGIS at the C level and all it involve in term of release management or to return WKT geometries and thus using PostGIS just at the SQL level, facilitating supports for former/newer PostGIS versions. An argument against linking with PostGIS is available at this location: http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationWorking01#WhyisitimportnattoavoidlinkingwithPostGIS The most cited tool to do such operation right now is StarSpan (http://starspan.projects.atlas.ca.gov) There is plan to implement a raster/raster intersection function as well but the exact result of such an operation is still to determine. Thanks for your thoughts. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RFQ: WKT Raster SQL API for being able to intersect vector and raster tables
Hi PostGIS folks, Now that you have a little bit of time after the release of PostGIS 1.5, I would like to request your comments on the WKT Raster specifications drawn to meet objective 0.1.6 - "Being able to intersect vector and raster to produce vector". In other word: what set of function do we need to be able to intersect a geometry layer with a raster layer? You will find the last version of the written specs here: http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationWorking01#Objective0.1.6e-Beingabletointersectvectorandrastertoproducevector This kind of operation is a generalisation of the operation needed by many people doing spatial analysis involving raster and vector. Some examples of users expressing their needs for a similar functionality: http://postgis.refractions.net/pipermail/postgis-users/2009-April/023218.html http://postgis.refractions.net/pipermail/postgis-users/2009-March/022890.html Some from the r-sig-geo group: https://stat.ethz.ch/pipermail/r-sig-geo/2009-December/007177.html https://stat.ethz.ch/pipermail/r-sig-geo/2009-November/006999.html We plan on reusing as much as possible existing PostGIS functions. The basic idea is to convert (only the) WKT Raster tiles involved in the intersect to geometries and then to procede to a normal vector intersection using the PostGIS intersection function. It involves functions like: ST_Envelope(raster), ST_ConvexHull(raster), ST_Shape(raster, integer), ST_AsPolygon(raster, integer), ST_AsWKTPolygon(raster, integer), ST_Intersects(raster, integer, geometry), ST_Intersection(raster, integer, geometry). Jorge is in charge of the implementation. We are now in the process of testing if it is worth using the polygonize function of GDAL, thus linking with GDAL, or implementing our own. A summany of the pros and cons is available at this location: http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationWorking01#TheprosandconsofusingGDALvsimplementingourownrasterservices We are also discussing the pros and cons of directly returning PostGIS geometry objects, thus having to link with PostGIS at the C level and all it involve in term of release management or to return WKT geometries and thus using PostGIS just at the SQL level, facilitating supports for former/newer PostGIS versions. An argument against linking with PostGIS is available at this location: http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationWorking01#WhyisitimportnattoavoidlinkingwithPostGIS The most cited tool to do such operation right now is StarSpan (http://starspan.projects.atlas.ca.gov) There is plan to implement a raster/raster intersection function as well but the exact result of such an operation is still to determine. Thanks for your thoughts. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Objetcs inside the polygon.!
César; Probably you want st_intersects(blocks,polygons) Chris Hermansenchris.herman...@timberline.ca tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625 Timberline Natural Resource Group · www.timberline.ca 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5 -Original Message- From: César Medina Date: Mon, 8 Feb 2010 11:08:09 To: Subject: [postgis-users] Objetcs inside the polygon.! ___ 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] [postgis-devel] Has anyone seen this before?
Haven't finished analyzing the situation yet, but don't think its bad RAM as I had originally suspected. The reason is as follows 1) If I take this original good table -- restore it on a PostGIS 1.4 database on the same server and repeat the copy process, it works fine. 2) Restoring it on their production server (different server), which is a 1.3.6 and then repeating the process gives the same problem. This database is a backup of their production database, so could be some corruption in the original production. So next step is to create a clean 1.3.6 database on this dev server and repeat the process to rule out 1.3.6 as the culprit. Hope to get around to doing that sometime today. Thanks, Regina -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Monday, February 08, 2010 8:21 AM To: PostGIS Users Discussion Cc: 'PostGIS Development Discussion' Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this before? Mark, On 08/02/2010, at 17:10 , Mark Cave-Ayland wrote: > Paragon Corporation wrote: > >> It should be noted that for this table the_geom is the first field in >> the table and addr_num_tlid comes right after and is a varchar I can >> select any integer or bigint field fine, but selecting any text or >> varchar field results in the ERROR: invalid memory alloc request >> size 18446744073709551613 > > Yup, this is totally wrong. I suspect bad RAM and/or disk on the machine in question. For only my own benefit possibly, but is this because the geometry column is being stored outside the normal table pages (the TOAST bit)? cheers Ben ___ 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] UPDATE THE_GEOM IN MY TABLE
2010/2/8 eehab hamzeh
>
> I am trying to update the_geom in my table i use the following sql statement
> i receive error
>
> update vertix set the_geom = GeometryFromText('point(select x from vertix ||
> select y from vertix || select z from vertix)')
Much simpler is:
update vertix set the_geom = ST_MakePoint(x, y, z);
Also, rather than keeping both numeric and geometry representations of
the same data, you might find it useful to use updateable views to
show the numeric representation. This way, you can modify the numbers
directly to change the geometry and vice versa, so you don`t have to
synchronize (update) the representations if either change. I have code
if you`d like to see, let me know.
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] UPDATE THE_GEOM IN MY TABLE
Hello
I am trying to update the_geom in my table i use the following sql statement i
receive error
update vertix set the_geom = GeometryFromText('point(select x from vertix ||
select y from vertix || select z from vertix)')
below is my table schema
CREATE TABLE vertix
(
the_geom geometry,
id integer,
x numeric,
y numeric,
z numeric,
newpoint geometry
)
WITH (OIDS=FALSE);
ALTER TABLE vertix OWNER TO postgres;
Kind regards
> From: postgis-users-requ...@postgis.refractions.net
> Subject: postgis-users Digest, Vol 92, Issue 6
> To: postgis-users@postgis.refractions.net
> Date: Sat, 6 Feb 2010 12:00:01 -0800
>
> Send postgis-users mailing list submissions to
> postgis-users@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-requ...@postgis.refractions.net
>
> You can reach the person managing the list at
> postgis-users-ow...@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. Re: [postgis-devel] Has anyone seen this before?
> (Paragon Corporation)
>2. Re: [postgis-devel] Has anyone seen this before?
> (Paragon Corporation)
>3. Re: [postgis-devel] Has anyone seen this before?
> (Paragon Corporation)
>4. Re: Union a very big Multipolygon table with self
> intersections (Simon Greener)
>5. Query for shapes completely within a circle (Jordan, Thomas)
>6. Re: Query for shapes completely within a circle (Nicklas Av?n)
>
>
> --
>
> Message: 1
> Date: Fri, 5 Feb 2010 17:08:36 -0500
> From: "Paragon Corporation"
> Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this
> before?
> To: "'PostGIS Development Discussion'"
>
> Cc: 'PostGIS Users Discussion'
> Message-ID:
> Content-Type: text/plain; charset="us-ascii"
>
> Further note:
>
> Doing an ST_IsEmpty on one of these monsters shows its not empty.
>
> ST_GeometryType(..) returns ST_Geometry
>
> and when I try to do an ST_Mem_Size on one, I hmm get this
>
> ERROR: lwgeom_size called with unknown-typed serialized geometry
>
>
> When try ST_IsValid get this
>
> ERROR: Unknown geometry type: 0
>
>
> I'm going to try to repeat the process of copy from good table to see if the
> problem disappears. I suppose it could be disk corruption of some sort.
>
> Thanks,
> Regina
> -Original Message-
> From: postgis-devel-boun...@postgis.refractions.net
> [mailto:postgis-devel-boun...@postgis.refractions.net] On Behalf Of Paragon
> Corporation
> Sent: Friday, February 05, 2010 4:55 PM
> To: 'PostGIS Development Discussion'
> Subject: [postgis-devel] Has anyone seen this before?
>
> What would result in an UNKNOWN geometry type. One of our clients is
> getting this error when they try to vacuum analyze their table.
>
> > NOTICE: compute_serialized_box3d called on unknown type 0NOTICE:
> > compute_serialized_box3d called on unknown type 0 ...
>
> On closer inspection -- I see that there are UNKNOWNs and those UNKNOWNS are
> not NULL but the ST_AsText looks blank viewed via psql
>
>
> select count(*), GeometryType(the_geom) from badtable group by
> GeometryType(the_geom); count | geometrytype
> ---+--
> 98483 | MULTIPOLYGON
> 1517 | UNKNOWN
>
>
> This is running on
>
>
>
>
> POSTGIS="1.3.6" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.0, 21 Dec 2007"
> USE_STATS
>
> PostgreSQL 8.4.1 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
> 20
> 071124 (Red Hat 4.1.2-42), 64-bit
>
>
> They say the table was created by doing a
>
> create table badtable as select * from goodtable order by addr_num_tlid
> limit 10;
>
>
> the goodtable doesn't exhibit this bizarre behavior. Not sure if this is a
> PostgreSQL bug or PostGIS bug.
>
> Any thoughts?
>
>
> Thanks,
> Regina
>
>
> ___
> postgis-devel mailing list
> postgis-de...@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
>
>
> --
>
> Message: 2
> Date: Fri, 5 Feb 2010 17:29:50 -0500
> From: "Paragon Corporation"
> Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this
> before?
> To: "'PostGIS Users Discussion'"
> ,"'PostGIS Development
> Discussion'"
> Message-ID:
> Content-Type: text/plain; charset="us-ascii"
>
> Okay just tried this and the problem is reproducible.
>
> The goodtable
> select count(*), GeometryType(the_geom) from goodtable
> group by GeometryType(the_geom)
>
> countGeometry Type
> 174007 MULTIPOLYGON
[postgis-users] Objetcs inside the polygon.!
Dear all.!! I am trying to do a script, but i dont know all the functions for this.! I have two (2) layer in posgtis format, the first is a polygon and the other is many block's but i need just every block that is inside the polygon thank you very much!!! César http://www.linkedin.com/in/cesarmedinam http://foss4gchile.blogspot.com/ mail: ciesareMedina (at) gmail (dot) com msn: ciesareMedina (at) hotmail (dot) com skype: ciesare_medina _ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. https://signup.live.com/signup.aspx?id=60969___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Has anyone seen this before?
Mark, On 08/02/2010, at 17:10 , Mark Cave-Ayland wrote: > Paragon Corporation wrote: > >> It should be noted that for this table >> the_geom is the first field in the table and addr_num_tlid comes right after >> and is a varchar >> I can select any integer or bigint field fine, but selecting any text or >> varchar field results in the ERROR: invalid memory alloc request size >> 18446744073709551613 > > Yup, this is totally wrong. I suspect bad RAM and/or disk on the machine in > question. For only my own benefit possibly, but is this because the geometry column is being stored outside the normal table pages (the TOAST bit)? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Character Primary Key
Hi Fabrice, AFAIK this is still true for the latest QGIS versions. Anyway: I'd ask again on the qgis-userlist if I were you: http://lists.osgeo.org/mailman/listinfo/qgis-user Regards, Marc F T wrote: Hello List QGis used to deal only with integer (INT4) primary keys (QGis 1.2 PostgreSQL/Postgis 8.2). Do you know if it is still the case (QGis 1.4)? It is annoying for us that QGis can't use existing character primary key... We have to add an artificial integer primary key and transform the existing character primary key as a unique non null constraint. This change is ok if you only use QGis. But if the data are used by an other soft that was used do deal with this character primary key, this cause an interoperability problem. Does QGis still only deal with INT4 primary key? If the response is yes, is this expected to change in a further version? Thanks Fabrice ___ 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] [postgis-devel] Has anyone seen this before?
Paragon Corporation wrote: It should be noted that for this table the_geom is the first field in the table and addr_num_tlid comes right after and is a varchar I can select any integer or bigint field fine, but selecting any text or varchar field results in the ERROR: invalid memory alloc request size 18446744073709551613 Yup, this is totally wrong. I suspect bad RAM and/or disk on the machine in question. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Character Primary Key
Hello List QGis used to deal only with integer (INT4) primary keys (QGis 1.2 PostgreSQL/Postgis 8.2). Do you know if it is still the case (QGis 1.4)? It is annoying for us that QGis can't use existing character primary key... We have to add an artificial integer primary key and transform the existing character primary key as a unique non null constraint. This change is ok if you only use QGis. But if the data are used by an other soft that was used do deal with this character primary key, this cause an interoperability problem. Does QGis still only deal with INT4 primary key? If the response is yes, is this expected to change in a further version? Thanks Fabrice ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
