[postgis-users] UPDATE THE_GEOM IN MY TABLE
eehab hamzeh
eehab40 at hotmail.com
Mon Feb 8 06:45:07 PST 2010
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-request at postgis.refractions.net
> Subject: postgis-users Digest, Vol 92, Issue 6
> To: postgis-users at postgis.refractions.net
> Date: Sat, 6 Feb 2010 12:00:01 -0800
>
> 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. 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" <lr at pcorp.us>
> Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this
> before?
> To: "'PostGIS Development Discussion'"
> <postgis-devel at postgis.refractions.net>
> Cc: 'PostGIS Users Discussion' <postgis-users at postgis.refractions.net>
> Message-ID: <B6AEBDD6349F4785B14D2461EAF63E89 at b>
> 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-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at 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 100000;
>
>
> 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-devel at 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" <lr at pcorp.us>
> Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this
> before?
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>, "'PostGIS Development
> Discussion'" <postgis-devel at postgis.refractions.net>
> Message-ID: <B25A55E3B1914773BF6CA20E99D15F14 at b>
> 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)
>
> count Geometry Type
> 174007 MULTIPOLYGON
>
>
> Repeat process:
> create table badtable as select * from goodtable order by addr_num_tlid
> limit 100000;
>
> Same bizarre result.
>
> Even more bizzarre - I can do this
>
> select GeometryType(the_geom) from ca1.ca_toscrub_hits_st_union1
> WHERE GeometryType(the_geom) = 'UNKNOWN'
> limit 1;
>
> But If I try to do this
>
> SELECT addr_num_tlid, GeometryType(the_geom) from
> ca1.ca_toscrub_hits_st_union1
> WHERE GeometryType(the_geom) = 'UNKNOWN'
> limit 1
>
> or this
> SELECT addr_num_tlid from ca1.ca_toscrub_hits_st_union1
> WHERE GeometryType(the_geom) = 'UNKNOWN'
> limit 1
>
> I get this error:
>
> ERROR: invalid memory alloc request size 18446744073709551613
>
> Very puzzled Regina
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
> Corporation
> Sent: Friday, February 05, 2010 5:09 PM
> To: 'PostGIS Development Discussion'
> Cc: 'PostGIS Users Discussion'
> Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this before?
>
> 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-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at 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 100000;
>
>
> 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-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> ------------------------------
>
> Message: 3
> Date: Fri, 5 Feb 2010 17:37:22 -0500
> From: "Paragon Corporation" <lr at pcorp.us>
> Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this
> before?
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>, "'PostGIS Development
> Discussion'" <postgis-devel at postgis.refractions.net>
> Message-ID: <A078449A8E8B47CEBF2A65C1966B6485 at b>
> Content-Type: text/plain; charset="us-ascii"
>
> 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
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
> Corporation
> Sent: Friday, February 05, 2010 5:30 PM
> To: 'PostGIS Users Discussion'; 'PostGIS Development Discussion'
> Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this before?
>
> Okay just tried this and the problem is reproducible.
>
> The goodtable
> select count(*), GeometryType(the_geom) from goodtable group by
> GeometryType(the_geom)
>
> count Geometry Type
> 174007 MULTIPOLYGON
>
>
> Repeat process:
> create table badtable as select * from goodtable order by addr_num_tlid
> limit 100000;
>
> Same bizarre result.
>
> Even more bizzarre - I can do this
>
> select GeometryType(the_geom) from ca1.ca_toscrub_hits_st_union1 WHERE
> GeometryType(the_geom) = 'UNKNOWN'
> limit 1;
>
> But If I try to do this
>
> SELECT addr_num_tlid, GeometryType(the_geom) from
> ca1.ca_toscrub_hits_st_union1
> WHERE GeometryType(the_geom) = 'UNKNOWN'
> limit 1
>
> or this
> SELECT addr_num_tlid from ca1.ca_toscrub_hits_st_union1 WHERE
> GeometryType(the_geom) = 'UNKNOWN'
> limit 1
>
> I get this error:
>
> ERROR: invalid memory alloc request size 18446744073709551613
>
> Very puzzled Regina
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
> Corporation
> Sent: Friday, February 05, 2010 5:09 PM
> To: 'PostGIS Development Discussion'
> Cc: 'PostGIS Users Discussion'
> Subject: Re: [postgis-users] [postgis-devel] Has anyone seen this before?
>
> 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-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at 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 100000;
>
>
> 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-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
> _______________________________________________
> 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
>
>
>
>
> ------------------------------
>
> Message: 4
> Date: Sat, 06 Feb 2010 13:17:11 +1100
> From: "Simon Greener" <simon at spatialdbadvisor.com>
> Subject: Re: [postgis-users] Union a very big Multipolygon table with
> self intersections
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID: <op.u7oomxuizbn0g9 at spdba>
> Content-Type: text/plain; charset=iso-8859-1; format=flowed; delsp=yes
>
> How about something like this:
>
> select id, ST_Union(ST_Accum(case when ST_IsValid(a.the_geom) = 't' then a.the_geom else st_buffer(a.the_geom,0) end) as the_geom
> from (select id, the_geom
> from <<table>>
> where the_geom is not null
> and geometrytype(the_geom) != 'GEOMETRYCOLLECTION'
> union all
> SELECT gid, (ST_Dump(the_geom)).geom as the_geom
> from <<table>>
> where the_geom is not null
> and geometrytype(the_geom) = 'GEOMETRYCOLLECTION'
> ) as a;
>
> The split based on GEOMETRYCOLLECTION is that when I first tested the STR indexed ST_Union I found that
> performance was affectedif the union set includes an mpoly.
>
> regards
> SImon
> On Fri, 05 Feb 2010 19:44:51 +1100, ibrahim saricicek <ibrahimsaricicek at gmail.com> wrote:
>
> > Hi;
> >
> > create a new geometry column
> >
> > use update table set new_column=st_buffer(the_geom,0). IsValid will return
> > true. Then try the union operation..
> >
> > IBO..
> >
> > On Fri, Feb 5, 2010 at 12:46 AM, Javier de la Torre
> > <jatorre at vizzuality.com>wrote:
> >
> >> Hi all,
> >>
> >> I have a table with a MULTIPOLYGON field with around 100k records. Some of
> >> these records, 2K have self intersections and other problems that make
> >> ST_IsValid return false. The polygons overlap a lot and I wanted to generate
> >> another table that will be the union of all polygons.
> >>
> >> The table looks like
> >> id, the_geom
> >>
> >> What would be the best way to union all of the geometries into a new table
> >> where there is only POLYGONS that do not overlap?
> >>
> >> Thanks in advance.
> >>
> >> Javier.
> >>
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
>
>
> --
> SpatialDB Advice and Design, Solutions Architecture and Programming,
> Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
> Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
> 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
> Website: www.spatialdbadvisor.com
> Email: simon at spatialdbadvisor.com
> Voice: +61 362 396397
> Mobile: +61 418 396391
> Skype: sggreener
> Longitude: 147.20515 (147? 12' 18" E)
> Latitude: -43.01530 (43? 00' 55" S)
> GeoHash: r22em9r98wg
> NAC:W80CK 7SWP3
>
>
> ------------------------------
>
> Message: 5
> Date: Sat, 6 Feb 2010 12:12:58 -0500
> From: "Jordan, Thomas" <tjorda01 at harris.com>
> Subject: [postgis-users] Query for shapes completely within a circle
> To: <postgis-users at postgis.refractions.net>
> Cc: "Jordan, Thomas" <tjorda01 at harris.com>
> Message-ID:
> <5972B09E3EECF748AFF4BBE2B705812EB56DD1 at mlbe2k11.cs.myharris.net>
> Content-Type: text/plain; charset="us-ascii"
>
> Using ST_DWithin(geom, geomPt, radius) returns all geom that
> 'intersects' any part of the circle's area (acts like ST_Intersects).
>
> What I would like, however, is only those geom 'completely within the
> circle'.
> For instance, if the point is Orlando, FL and the radius is 2 miles, the
> ST_Dwithin would return a shapes like the state Florida and the whole
> US; where I only want the buildings in Orlando.
>
> This is the closest approximation I have so far:
>
> SELECT id, geom
> FROM geospatial
> WHERE ST_WITHIN(geom, ST_Expand(geometryFromText(geomPt, 4326), radius)
> )
>
> This is much better, but it still returns some geom that are outside of
> the circle; specifically all geom completely within the superscribing
> box returned by ST_EXPAND.
>
> Thanks for your help
> Tom
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100206/75bb77cf/attachment-0001.html>
>
> ------------------------------
>
> Message: 6
> Date: Sat, 6 Feb 2010 19:52:10 +0100
> From: Nicklas Av?n <nicklas.aven at jordogskog.no>
> Subject: Re: [postgis-users] Query for shapes completely within a
> circle
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <201002061852.o16IqApW012227 at mail-core.space2u.com>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Hallo You have at least two options here. 1.instead of expand use st_buffer to make a circle and then use st_within the way you have done, something like:SELECT id, geom
> FROM geospatial
> WHERE ST_Within(geom, ST_Buffer(geomPt,radius) )
> This is also an approximation since st_buffer defults to build the circle from 32 segments, but that can be modified to a higher number if nessecary.http://postgis.org/documentation/manual-1.5/ST_Buffer.html 2.The other approach needs the latest postgis released a few days ago. I fyou have that installed you can try the new function ST_DFullywithin.http://postgis.org/documentation/manual-1.5/ST_DFullyWithin.html In your case it would be something like:SELECT id, geom
> FROM geospatial
> WHERE ST_DFullyWithin(geom, geomPt, radius) Hope that helps/Nicklas
> 2010-02-06 Jordan Thomas wrote:
>
>
> Using ST_DWithin(geom, geomPt, radius) returns all geom that 'intersects' any part of the circle's area (acts like ST_Intersects).
> What I would like, however, is only those geom 'completely within the circle'.
> >For instance, if the point is Orlando, FL and the radius is 2 miles, the ST_Dwithin would return a shapes like the state Florida and the whole US; where I only want the buildings in Orlando.
> This is the closest approximation I have so far:
> SELECT id, geom
> >FROM geospatial
> >WHERE ST_WITHIN(geom, ST_Expand(geometryFromText(geomPt, 4326), radius) )
> This is much better, but it still returns some geom that are outside of the circle; specifically all geom completely within the superscribing box returned by ST_EXPAND.
> Thanks for your help
> >Tom
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100206/3f8c4018/attachment-0001.html>
>
> ------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 92, Issue 6
> ********************************************
_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100208/35f5e769/attachment.html>
More information about the postgis-users
mailing list