Re: [postgis-users] Error reading OID from Table. OID Column has null value
By the way, as the error occured when reading the table, did you check your table have a primary key ? (mine had integer one) Nicolas On 31 October 2012 17:17, Nicolas Ribot wrote: > Hi Mark, > > Just quickly tested ArcGIS 10.1 with Postgis and query layers: > > The table for the test comes from a shapefile, loaded into postgis (WGS84). > I can define a query layer from it and display it in ArcGIS (ArcMap ArcInfo). > > The error you mentioned rise when trying to export this layer to a File GDB. > I then recreate the postgis table, forcing OIDS to be added to the > table (with oids clause) and the export went fine. > > So I understand that Query layers need to work on tables defined with > OIDS (which is not the default since several releases of PG) > > HTH > > Nicolas > > On 30 October 2012 14:38, Mark Volz wrote: >> Hello, >> >> >> I am still trying to figure out query layers and ArcGIS, I am getting an >> error when I try to open up a table: "Error reading OID from Table OID >> Column has null value." I noticed that when the table opens (blank) that >> there is an additional column called ESRI_OID. How can I add the missing >> column? >> >> Thanks >> >> >> >> >> Mark Volz >> GIS Specialist >> >> _______ >> 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] Error reading OID from Table. OID Column has null value
Hi Mark, Just quickly tested ArcGIS 10.1 with Postgis and query layers: The table for the test comes from a shapefile, loaded into postgis (WGS84). I can define a query layer from it and display it in ArcGIS (ArcMap ArcInfo). The error you mentioned rise when trying to export this layer to a File GDB. I then recreate the postgis table, forcing OIDS to be added to the table (with oids clause) and the export went fine. So I understand that Query layers need to work on tables defined with OIDS (which is not the default since several releases of PG) HTH Nicolas On 30 October 2012 14:38, Mark Volz wrote: > Hello, > > > I am still trying to figure out query layers and ArcGIS, I am getting an > error when I try to open up a table: "Error reading OID from Table OID > Column has null value." I noticed that when the table opens (blank) that > there is an additional column called ESRI_OID. How can I add the missing > column? > > Thanks > > > > > Mark Volz > GIS Specialist > > ___________ > 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] Question about restoring a dump
If you can [1] use postgis_restore rather than pg_restore, as described in the Hard Upgrade section of the manual: http://www.postgis.net/documentation/manual-2.0SVN/postgis_installation.html#hard_upgrade [1] you can use it only if your dump was made using custom format (-Fc switch to pg_dump) --strk; http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net On Wed, Oct 31, 2012 at 07:40:15AM -0700, lynx86 wrote: > Hi all > I'm new to PostGIS and have a question about restoring a PostGIS dump into a > new created PostGIS database. > > The dump was made in a database system using PostGIS version 1.4. > It was given to me and I now tried to restore it in my database system > (PostgreSQL 9.2, PostGIS 2.0, OS: Windows 7 64 bit). > I followed the instructions given in > http://postgis.refractions.net/documentation/manual-2.0/postgis_installation.html#create_new_db_extensions > and then tried to restore the dump by the following commands: > > pg_restore -U postgres -f temp_dump myDump.backup > psql -U postgres -L logging_output myDatabase < temp_dump > > As result I get errors like > ERROR: could not access file "$libdir/postgis-1.4": No such file or > directory > > By the help of my logging_output file, I could detect the corresponding > command: > CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d > LANGUAGE c IMMUTABLE STRICT > AS '$libdir/postgis-1.4', 'BOX2DFLOAT4_in'; > > As mentioned, I use PostGIS 2.0, so of course $libdir/postgis-1.4 doesn't > exist. > How can I solve this problem? > > Thank you! > Christoph > > > > > -- > View this message in context: > http://postgis.17.n6.nabble.com/Question-about-restoring-a-dump-tp5000887.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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Question about restoring a dump
Hi all I'm new to PostGIS and have a question about restoring a PostGIS dump into a new created PostGIS database. The dump was made in a database system using PostGIS version 1.4. It was given to me and I now tried to restore it in my database system (PostgreSQL 9.2, PostGIS 2.0, OS: Windows 7 64 bit). I followed the instructions given in http://postgis.refractions.net/documentation/manual-2.0/postgis_installation.html#create_new_db_extensions and then tried to restore the dump by the following commands: pg_restore -U postgres -f temp_dump myDump.backup psql -U postgres -L logging_output myDatabase < temp_dump As result I get errors like ERROR: could not access file "$libdir/postgis-1.4": No such file or directory By the help of my logging_output file, I could detect the corresponding command: CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d LANGUAGE c IMMUTABLE STRICT AS '$libdir/postgis-1.4', 'BOX2DFLOAT4_in'; As mentioned, I use PostGIS 2.0, so of course $libdir/postgis-1.4 doesn't exist. How can I solve this problem? Thank you! Christoph -- View this message in context: http://postgis.17.n6.nabble.com/Question-about-restoring-a-dump-tp5000887.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] output size of raster image increase more than original
the output image is equal amount of your tiles... *Jeff Lake* MichiganWxSystem <http://www.michiganwxsystem.com> AllisonHouse <http://www.allisonhouse.com> GRLevelXStuff <http://www.grlevelxstuff.com> On 10/31/2012 6:51, Mahavir Trivedi wrote: hi i have tif image of 15198 X 15919 as import using raster2pgsql 100x100 tile command. but when i export it using gdal_translate output image 15200X16000 . pls give me solution. with regards mahavir _______ 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] output size of raster image increase more than original
hi i have tif image of 15198 X 15919 as import using raster2pgsql 100x100 tile command. but when i export it using gdal_translate output image 15200X16000 . pls give me solution. with regards mahavir ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Null results from geocode_intersection, postgis 2.0.1, postgreSQL 9.1.6
Friends,
geocode_intersection returns nothing. I'm a newbie!...So, I'd like some ideas.
First, the postgis (2.0.1) and PostgreSQL 9.1.6 versions:
POSTGIS="2.0.1 r9979" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23
September 2009" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8"
LIBJSON="UNKNOWN" RASTER
PostgreSQL 9.1.6 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.6.3-1ubuntu5) 4.6.3, 32-bit
(1 row)
These commands return expected results:
SELECT pprint_addy(normalize_address('100 NW 10th Ave, Portland,
Oregon 97239'));
SELECT geomout FROM geocode_intersection( 'NW Davis', 'NW 10th',
'OR', 'PORTLAND');
select * from geocode('100 NW 10th Ave, Portland, OR 97239');
This ireturns nothing, even wihen I change the case, add a zipcode..
alter the names of the streets to include Ave/St...
select * from geocode_intersection('NW Davis', 'NW 10th', 'OR', 'Portland');
No results from this either:
SELECT addy, geomout,rating
FROM geocode_intersection('nw davis st ', 'nw 10 ave', 'or',
'portland', '', 1);
Do I reload Oregon Data. Correct a typo? Reinstall postgis?
Reinstall the function?
thanks,
--
Katie Urey
ksu...@gmail.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] PostGIS 1.5.5 and PostgreSQL 9.2
Happy Halowe'en, it's online. http://postgis.org/download P. On Tue, Oct 30, 2012 at 9:47 AM, Sandro Santilli wrote: > On Tue, Oct 30, 2012 at 09:45:24AM -0700, Paul Ramsey wrote: >> No, we don't have a calendrical release schedule, but the backlog of >> fixes complete for 1.5.6 is actually a pretty impressive nest of >> crashers and improvements, so I'm sure we could be poked into it... >> >> - Bug Fixes >>- #547, ST_Contains memory problems, the remake >>- #1957, ST_Distance to a one-point LineString returns NULL >>- #1936, ST_GeomFromGML on CurvePolygon causes server crash >>- #1953, Segfault on GEOS calls with empty polygon >>- #1976, Geography point-in-ring code overhauled for more reliability >>- #2071, Add PgSQL 9.2 support > > +1, would make a good halloween release --strk; > > --strk; > > http://www.cartodb.com - Map, analyze and build applications with your data > > ~~ http://strk.keybit.net > > ___ > 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] PostGIS 1.5.5 and PostgreSQL 9.2
On Tue, Oct 30, 2012 at 09:45:24AM -0700, Paul Ramsey wrote: > No, we don't have a calendrical release schedule, but the backlog of > fixes complete for 1.5.6 is actually a pretty impressive nest of > crashers and improvements, so I'm sure we could be poked into it... > > - Bug Fixes >- #547, ST_Contains memory problems, the remake >- #1957, ST_Distance to a one-point LineString returns NULL >- #1936, ST_GeomFromGML on CurvePolygon causes server crash >- #1953, Segfault on GEOS calls with empty polygon >- #1976, Geography point-in-ring code overhauled for more reliability >- #2071, Add PgSQL 9.2 support +1, would make a good halloween release --strk; --strk; http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net _______________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2
No, we don't have a calendrical release schedule, but the backlog of fixes complete for 1.5.6 is actually a pretty impressive nest of crashers and improvements, so I'm sure we could be poked into it... - Bug Fixes - #547, ST_Contains memory problems, the remake - #1957, ST_Distance to a one-point LineString returns NULL - #1936, ST_GeomFromGML on CurvePolygon causes server crash - #1953, Segfault on GEOS calls with empty polygon - #1976, Geography point-in-ring code overhauled for more reliability - #2071, Add PgSQL 9.2 support P. On Tue, Oct 30, 2012 at 5:15 AM, Ilie, Radu wrote: > Thank you very much for the prompt response. Do you have at least a rough > estimate for when the next patch release is going to be? > > Radu Ilie > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paul > Ramsey > Sent: Monday, October 29, 2012 2:04 PM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2 > > No, it was not supported, it is in svn now, and will be at next patch release > > http://trac.osgeo.org/postgis/ticket/2071 > > P. > > On Mon, Oct 29, 2012 at 7:04 AM, Ilie, Radu wrote: >> Yes, I did. The error is this: >> >> >> >> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith >> -Wdeclaration-after-statement -Wendif-labels >> -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing >> -fwrapv -g -fpic >> -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/ >> INSTALL/usr/local/pgsql/include >> -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/ >> INSTALL/usr/local/pgsql/include >> -I/usr/include/libxml2 -I../liblwgeom -I. -I. >> -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/ >> INSTALL/usr/local/pgsql/include/server >> -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/internal >> -D_GNU_SOURCE -I/usr/include/libxml2 -c -o lwgeom_estimate.o >> lwgeom_estimate.c >> >> lwgeom_estimate.c: In function 'LWGEOM_gist_joinsel': >> >> lwgeom_estimate.c:318: error: 'Form_pg_class' undeclared (first use in >> this >> function) >> >> lwgeom_estimate.c:318: error: (Each undeclared identifier is reported >> only once >> >> lwgeom_estimate.c:318: error: for each function it appears in.) >> >> lwgeom_estimate.c:318: error: expected ';' before 'reltup' >> >> lwgeom_estimate.c:319: error: 'reltup' undeclared (first use in this >> function) >> >> lwgeom_estimate.c:330: error: expected ';' before 'reltup' >> >> make[1]: *** [lwgeom_estimate.o] Error 1 >> >> >> >> Radu >> >> >> >> From: postgis-users-boun...@postgis.refractions.net >> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of >> Jeff Lake >> Sent: Monday, October 29, 2012 9:59 AM >> To: PostGIS Users Discussion >> Subject: Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2 >> >> >> >> Did you specify the path to pg_config ?? >> eg: >> ./configure --with-pgconfig=/usr/pgsql-9.2/bin/pg_config >> >> Jeff Lake >> MichiganWxSystem >> AllisonHouse >> GRLevelXStuff >> >> On 10/29/2012 9:49, Ilie, Radu wrote: >> >> Hi, >> >> >> >> I am getting build errors when trying to build PostGIS 1.5.5 against >> PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it >> will be fixed? >> >> >> >> Thank you, >> >> >> >> Radu Ilie >> >> >> >> >> >> >> _______ >> >> 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 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
tersects wasn't yet implemented for >>> >>> out of db storage. Looking through the trunk code, though, it appears >>> >>> that maybe you've gone ahead and implemented that since 2.0.1? If so, >>> >>> great! ST_PixelAsPoints() is another good reason for me to seriously >>> >>> consider working out of trunk... >>> >>> >>> >>>> Part of the problem is that >>> >>>> anything stored in PostgreSQL (in-db) is TOASTed so needs to be >>> >>>> deserialized (and probably decompressed). So, if the serialized raster >>> >>>> is big (more bands), the deTOASTing will take longer. >>> >>> >>> >>> Thanks; good to know. >>> >>> >>> >>>> Another problem with your benchmark query is that the ST_Clip() is >>> >>>> running twice (for height and width). >>> >>> >>> >>> Ah, that changes the picture pretty dramatically (see attached plot). >>> >>> Since it improves by a lot more than a factor of two, I suspect maybe >>> >>> I'm having some desktop scaling issues or something. I'll go ahead and >>> >>> actually put this on our database server, try the trunk version, and go >>> >>> from there. This is at least somewhat encouraging :) Thanks for the >>> >>> suggestions. >>> >>> >>> >>> ~James >>> >>> >>> >>> On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote: >>> >>>> James, >>> >>>> >>> >>>> I use PostGIS raster for a similar purpose (model outputs) though my >>> >>>> model outputs are for a specific day (average temperature for a >>> >>>> specific >>> >>>> date). So, one raster with one band per day per variable. I could >>> >>>> combine a year's worth of bands into one raster but I decided against >>> >>>> that. >>> >>>> >>> >>>> If you've got a large number of bands (100s or more), you may want to >>> >>>> consider having the rasters be out-of-db. Part of the problem is that >>> >>>> anything stored in PostgreSQL (in-db) is TOASTed so needs to be >>> >>>> deserialized (and probably decompressed). So, if the serialized raster >>> >>>> is big (more bands), the deTOASTing will take longer. >>> >>>> >>> >>>> Another problem with your benchmark query is that the ST_Clip() is >>> >>>> running twice (for height and width). >>> >>>> >>> >>>> If you're in the evaluation stage and you're compiling PostGIS >>> >>>> yourself, >>> >>>> I'd recommend trying SVN -trunk (will become 2.1) as it has additional >>> >>>> capabilities and performance improvements. I'm already using -trunk in >>> >>>> production as I needed the new features (full disclosure: I wrote >>> >>>> almost >>> >>>> the new features in -trunk). >>> >>>> >>> >>>> -bborie >>> >>>> >>> >>>> On 10/29/2012 03:32 PM, James Hiebert wrote: >>> >>>>> Hi All, >>> >>>>> >>> >>>>> I'm considering using PostGIS rasters for storage of raster data at >>> >>>>> my organization and I'm looking for some advice (or perhaps a reality >>> >>>>> check). I work for a region climate services provider and the vast >>> >>>>> majority of our data (by volume, not necessarily complexity) are >>> >>>>> output from climate models. These are generally a n-by-m raster with >>> >>>>> one band for each timestep. There could be upwards of 36k to 72k >>> >>>>> timesteps for a typical model run. We have hundreds of model runs. >>> >>>>> >>> >>>>> So my question is, is it insane to be thinking of storing that many >>> >>>>> bands in a PostGIS raster? Or more specifically, is this _not_ a use >>> >>>>> case for which PostGIS rasters were designed? I notice that most of >>> >>>>> the examples in the docs and in "PostGI
Re: [postgis-users] Is this a perverse use-case for raster types?
ing stored in PostgreSQL (in-db) is TOASTed so needs to be
>> >>>> deserialized (and probably decompressed). So, if the serialized raster
>> >>>> is big (more bands), the deTOASTing will take longer.
>> >>>
>> >>> Thanks; good to know.
>> >>>
>> >>>> Another problem with your benchmark query is that the ST_Clip() is
>> >>>> running twice (for height and width).
>> >>>
>> >>> Ah, that changes the picture pretty dramatically (see attached plot).
>> >>> Since it improves by a lot more than a factor of two, I suspect maybe
>> >>> I'm having some desktop scaling issues or something. I'll go ahead and
>> >>> actually put this on our database server, try the trunk version, and go
>> >>> from there. This is at least somewhat encouraging :) Thanks for the
>> >>> suggestions.
>> >>>
>> >>> ~James
>> >>>
>> >>> On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote:
>> >>>> James,
>> >>>>
>> >>>> I use PostGIS raster for a similar purpose (model outputs) though my
>> >>>> model outputs are for a specific day (average temperature for a specific
>> >>>> date). So, one raster with one band per day per variable. I could
>> >>>> combine a year's worth of bands into one raster but I decided against
>> >>>> that.
>> >>>>
>> >>>> If you've got a large number of bands (100s or more), you may want to
>> >>>> consider having the rasters be out-of-db. Part of the problem is that
>> >>>> anything stored in PostgreSQL (in-db) is TOASTed so needs to be
>> >>>> deserialized (and probably decompressed). So, if the serialized raster
>> >>>> is big (more bands), the deTOASTing will take longer.
>> >>>>
>> >>>> Another problem with your benchmark query is that the ST_Clip() is
>> >>>> running twice (for height and width).
>> >>>>
>> >>>> If you're in the evaluation stage and you're compiling PostGIS yourself,
>> >>>> I'd recommend trying SVN -trunk (will become 2.1) as it has additional
>> >>>> capabilities and performance improvements. I'm already using -trunk in
>> >>>> production as I needed the new features (full disclosure: I wrote almost
>> >>>> the new features in -trunk).
>> >>>>
>> >>>> -bborie
>> >>>>
>> >>>> On 10/29/2012 03:32 PM, James Hiebert wrote:
>> >>>>> Hi All,
>> >>>>>
>> >>>>> I'm considering using PostGIS rasters for storage of raster data at my
>> >>>>> organization and I'm looking for some advice (or perhaps a reality
>> >>>>> check). I work for a region climate services provider and the vast
>> >>>>> majority of our data (by volume, not necessarily complexity) are
>> >>>>> output from climate models. These are generally a n-by-m raster with
>> >>>>> one band for each timestep. There could be upwards of 36k to 72k
>> >>>>> timesteps for a typical model run. We have hundreds of model runs.
>> >>>>>
>> >>>>> So my question is, is it insane to be thinking of storing that many
>> >>>>> bands in a PostGIS raster? Or more specifically, is this _not_ a use
>> >>>>> case for which PostGIS rasters were designed? I notice that most of
>> >>>>> the examples in the docs and in "PostGIS In Action" focus only on
>> >>>>> images and I can imagine that handling multispectral satellite images
>> >>>>> as being more of the intended use case.
>> >>>>>
>> >>>>> I did a little benchmarking of a typical use case of ours ("What's the
>> >>>>> average temperature inside a some polygon, e.g. a river basin?"). I
>> >>>>> noticed that the run time for doing a ST_Clip(raster, band, geometry)
>> >>>>> and ST_Intersects(raster, band, geometry) appears to be super-linear
>> >>>>> even when doing it on just a single band. I ran the following query:
>> >>>>> SELECT rid, st_height(st_clip(rast, 1, the_geom)),
>> >>>>> st_width(st_clip(rast, the_geom)) FROM basins INNER JOIN bcsd ON
>> >>>>> ST_Intersects(rast, 1, the_geom) WHERE rid = (where basins is
>> >>>>> table of river basins with one single polygon and bcsd is a table with
>> >>>>> a raster column "rast").
>> >>>>> for a set of rasters with increasing number of bands, and the time to
>> >>>>> run the query is shown in the attached plot. Since the raster
>> >>>>> properties are presumably shared across all the bands, it seems odd to
>> >>>>> me that run time would increase. I would expect it to be _contant_
>> >>>>> (with constant number of pixels), but I suppose that that's my own
>> >>>>> ignorance as to how the PG type extensions work?
>> >>>>>
>> >>>>> Comments or explanations are welcome.
>> >>>>>
>> >>>>> ~James
>> >
>>
>> --
>> Bborie Park
>> Programmer
>> Center for Vectorborne Diseases
>> UC Davis
>> 530-752-8380
>> bkp...@ucdavis.edu
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> --
> James Hiebert
> Lead, Computational Support
> Pacific Climate Impacts Consortium
> http://www.pacificclimate.org
> Room 112, University House 1, University of Victoria
> PO Box 1700 Sta CSC, Victoria, BC V8V 2Y2
> E-mail: hieb...@uvic.ca
> Tel: (250) 472-4521
> Fax: (250) 472-4830
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Error reading OID from Table. OID Column has null value
Hello, I am still trying to figure out query layers and ArcGIS, I am getting an error when I try to open up a table: "Error reading OID from Table OID Column has null value." I noticed that when the table opens (blank) that there is an additional column called ESRI_OID. How can I add the missing column? Thanks Mark Volz GIS Specialist _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2
Thank you very much for the prompt response. Do you have at least a rough estimate for when the next patch release is going to be? Radu Ilie -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paul Ramsey Sent: Monday, October 29, 2012 2:04 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2 No, it was not supported, it is in svn now, and will be at next patch release http://trac.osgeo.org/postgis/ticket/2071 P. On Mon, Oct 29, 2012 at 7:04 AM, Ilie, Radu wrote: > Yes, I did. The error is this: > > > > gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith > -Wdeclaration-after-statement -Wendif-labels > -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing > -fwrapv -g -fpic > -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/ > INSTALL/usr/local/pgsql/include > -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/ > INSTALL/usr/local/pgsql/include > -I/usr/include/libxml2 -I../liblwgeom -I. -I. > -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/ > INSTALL/usr/local/pgsql/include/server > -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/internal > -D_GNU_SOURCE -I/usr/include/libxml2 -c -o lwgeom_estimate.o > lwgeom_estimate.c > > lwgeom_estimate.c: In function 'LWGEOM_gist_joinsel': > > lwgeom_estimate.c:318: error: 'Form_pg_class' undeclared (first use in > this > function) > > lwgeom_estimate.c:318: error: (Each undeclared identifier is reported > only once > > lwgeom_estimate.c:318: error: for each function it appears in.) > > lwgeom_estimate.c:318: error: expected ';' before 'reltup' > > lwgeom_estimate.c:319: error: 'reltup' undeclared (first use in this > function) > > lwgeom_estimate.c:330: error: expected ';' before 'reltup' > > make[1]: *** [lwgeom_estimate.o] Error 1 > > > > Radu > > > > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > Jeff Lake > Sent: Monday, October 29, 2012 9:59 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2 > > > > Did you specify the path to pg_config ?? > eg: > ./configure --with-pgconfig=/usr/pgsql-9.2/bin/pg_config > > Jeff Lake > MichiganWxSystem > AllisonHouse > GRLevelXStuff > > On 10/29/2012 9:49, Ilie, Radu wrote: > > Hi, > > > > I am getting build errors when trying to build PostGIS 1.5.5 against > PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it > will be fixed? > > > > Thank you, > > > > Radu Ilie > > > > > > > _______ > > 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 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] Pg_wrapper error
> Date: Fri, 26 Oct 2012 12:10:59 -0400
> From: lee.hachadooria...@gmail.com
> To: postgis-users@postgis.refractions.net
> Subject: Re: [postgis-users] Pg_wrapper error
>
> On Fri, Oct 26, 2012 at 11:27 AM, José Pedro Santos
> wrote:
> > I have the executables. When I try that (only shp2pgsql before I make the
> > connection with pg_wrapper) I can get the help commands. But now don't
> > because I enable the file with pg_wrapper, how can undo that?
> >
>
> When you created the symlink using the ln command, it would have
> overwritten the executables. If you backed them up first, delete the
> symlink and restore the executable from backup. If you didn't back
> them up, you'll have to reinstall PostGIS.
>
> --Lee
>
> --
> Lee Hachadoorian
> Asst Professor of Geography, Dartmouth College
> http://freecity.commons.gc.cuny.edu/
Hi Lee,
I undo the operation (ln - sf) now the error pg_wrapper don't appear but appear
one error saying that I don't have install the raster2pgsql. Also when I undo
the operation (ln -sf with rm -rf) I delete the pg_wrapper because the file
don't exist in the folder that should exist and the path for the files are
listen with red, that means broken symlinks.
Any solution? Thanks!
Jose Santos
> ___
> 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] Problem with 3D polygon intersection test
Got it working
SELECT gen_id,
ST_X((dp).geom),
ST_Y((dp).geom),
ST_Z((dp).geom)
FROM (
SELECT gen_id, ST_dumpPoints(geomtext) as dp
from vfaces
) As foo;
On Tue, Oct 30, 2012 at 11:19 AM, Francois Hugues wrote:
> **
> Maybe ST_X, ST_Y and ST_Z ?
>
> Hugues.
>
>
> ----------
> *De :* postgis-users-boun...@postgis.refractions.net [mailto:
> postgis-users-boun...@postgis.refractions.net] *De la part de* Ed Linde
> *Envoyé :* mardi 30 octobre 2012 11:14
> *À :* PostGIS Users Discussion
> *Objet :* Re: [postgis-users] Problem with 3D polygon intersection test
>
> Hi Nicolas,
> Not quite sure what to make of the output from ST_DumpPoints, how do I
> transform it to just raw x,y,z coordinates?
>
> select st_astext(geomtext) from small_tris where id=0;
>st_astext
> ---
> POLYGON Z ((10 10 805,20 10 809,10 20 814,10 10 805))
> (1 row)
>
> bounds=# select id, st_dumpPoints( geomtext ) from small_tris where id=0;
> id |st_dumppoints
> +--
> 0 | ("{1,1}",0101802440244000288940)
> 0 | ("{1,2}",0101803440244000488940)
> 0 | ("{1,3}",0101802440344000708940)
> 0 | ("{1,4}",0101802440244000288940)
> (4 rows)
>
>
> Thanks,Ed
>
>
> On Tue, Oct 30, 2012 at 11:05 AM, Ed Linde wrote:
>
>> Ok thanks!
>>
>>
>> On Tue, Oct 30, 2012 at 11:04 AM, Nicolas Ribot
>> wrote:
>>
>>> st_dumpRings will return the rings (closed linestrings) composing the
>>> polygon (exterior first, then interiors)
>>> and st_dumpPoints will return all the points forming the rings, in the
>>> right order.
>>>
>>> Nicolas
>>>
>>>
>>> On 30 October 2012 10:56, Ed Linde wrote:
>>>
>>>> Ok, for a polygon, is there a good postgis function that can return the
>>>> edges to me in the
>>>> right sequence? Or at least the points that constitute the polygon in
>>>> the right order, so I can
>>>> generate the edges in a script?
>>>>
>>>> Thanks,
>>>> Ed
>>>>
>>>>
>>>> On Tue, Oct 30, 2012 at 10:46 AM, Ed Linde wrote:
>>>>
>>>>> Ah drat! Ok then I have to basically do edge intersection tests by
>>>>> myself.
>>>>> Ah well. bad news! :(
>>>>>
>>>>>
>>>>> On Tue, Oct 30, 2012 at 10:44 AM, Nicolas Ribot <
>>>>> nicolas.ri...@gmail.com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> st_intersection is only suitable for 2D operations. Third dimension
>>>>>> is not taken into account.
>>>>>>
>>>>>> Nicolas
>>>>>>
>>>>>> On 30 October 2012 09:44, Ed Linde wrote:
>>>>>>
>>>>>>> Hi All,
>>>>>>> Is there a simpler test I can do? I am quite sure that I am probably
>>>>>>> not using the right
>>>>>>> function in my SQL, or not doing the intersection test correctly. Is
>>>>>>> there any simpler
>>>>>>> way to test this 3D intersection? Am waiting on the postgis gurus to
>>>>>>> come along and
>>>>>>> give me some ideas, coz I am at my wits ends on this one! :) The
>>>>>>> polygons look good
>>>>>>> to me when I visualize them, but don't get how all triangles are
>>>>>>> found as intersecting?
>>>>>>> Anyway will wait.
>>>>>>>
>>>>>>> Cheers,
>>>>>>> Ed
>>>>>>>
>>>>>>> On Tue, Oct 30, 2012 at 9:00 AM, Ed Linde wrote:
>>>>>>>
>>>>>>>> Hi All,
>>>>>>>> I am having some troubles getting the *ST_Intersect* function to
>>>>>>>> work with 3D polygons.
>>>>>>>> I have a table to hold triangles as polygons in *"small_tris"*
>>>>>>>> Another table for holding the polygonal faces of a 3D voronoi
>>>>>>>> diagram in *"vfaces"*
>>>>>&g
Re: [postgis-users] Problem with 3D polygon intersection test
Maybe ST_X, ST_Y and ST_Z ?
Hugues.
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Ed Linde
Envoyé : mardi 30 octobre 2012 11:14
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Problem with 3D polygon intersection test
Hi Nicolas,
Not quite sure what to make of the output from ST_DumpPoints, how do I
transform it to just raw x,y,z coordinates?
select st_astext(geomtext) from small_tris where id=0;
st_astext
---
POLYGON Z ((10 10 805,20 10 809,10 20 814,10 10 805))
(1 row)
bounds=# select id, st_dumpPoints( geomtext ) from small_tris where id=0;
id |st_dumppoints
+--
0 | ("{1,1}",0101802440244000288940)
0 | ("{1,2}",0101803440244000488940)
0 | ("{1,3}",0101802440344000708940)
0 | ("{1,4}",0101802440244000288940)
(4 rows)
Thanks,Ed
On Tue, Oct 30, 2012 at 11:05 AM, Ed Linde wrote:
Ok thanks!
On Tue, Oct 30, 2012 at 11:04 AM, Nicolas Ribot
wrote:
st_dumpRings will return the rings (closed linestrings)
composing the polygon (exterior first, then interiors)
and st_dumpPoints will return all the points forming the rings,
in the right order.
Nicolas
On 30 October 2012 10:56, Ed Linde wrote:
Ok, for a polygon, is there a good postgis function
that can return the edges to me in the
right sequence? Or at least the points that constitute
the polygon in the right order, so I can
generate the edges in a script?
Thanks,
Ed
On Tue, Oct 30, 2012 at 10:46 AM, Ed Linde
wrote:
Ah drat! Ok then I have to basically do edge
intersection tests by myself.
Ah well. bad news! :(
On Tue, Oct 30, 2012 at 10:44 AM, Nicolas Ribot
wrote:
Hi,
st_intersection is only suitable for 2D
operations. Third dimension is not taken into account.
Nicolas
On 30 October 2012 09:44, Ed Linde
wrote:
Hi All,
Is there a simpler test I can do? I am
quite sure that I am probably not using the right
function in my SQL, or not doing the
intersection test correctly. Is there any simpler
way to test this 3D intersection? Am
waiting on the postgis gurus to come along and
give me some ideas, coz I am at my wits
ends on this one! :) The polygons look good
to me when I visualize them, but don't
get how all triangles are found as intersecting?
Anyway will wait.
Cheers,
Ed
On Tue, Oct 30, 2012 at 9:00 AM, Ed
Linde wrote:
Hi All,
I am having some troubles getting the
ST_Intersect function to work with 3D polygons.
I have a table to hold triangles as
polygons in "small_tris"
Another table for holding the polygonal
faces of a 3D voronoi diagram in "vfaces"
So now I am trying to do an
intersection between the triangles and the voronoi faces
to get the triangles that intersect
with the faces. And it seems that *ALL* triangles
are intersecting. I have attached a PDF
where I have plotted the triangles and the
voronoi cells. so the box around in red
and blue with a major face going through is
Re: [postgis-users] Problem with 3D polygon intersection test
>>>> anyone needs more information to help, the please let me know! :) >>>>>>> >>>>>>> Cheers, >>>>>>> Ed >>>>>>> >>>>>>> *-- TABLE SCHEMAS* >>>>>>> bounds=# \d vfaces; >>>>>>> Table "public.vfaces" >>>>>>> Column | Type | Modifiers >>>>>>> --+--+--- >>>>>>> gen_id | integer | >>>>>>> geomtext | geometry | >>>>>>> >>>>>>> bounds=# \d small_tris; >>>>>>> Table "public.small_tris" >>>>>>> Column | Type | Modifiers >>>>>>> --+--+--- >>>>>>> id | integer | not null >>>>>>> v0 | integer | >>>>>>> v1 | integer | >>>>>>> v2 | integer | >>>>>>> geomtext | geometry | >>>>>>> Indexes: >>>>>>> "small_tris_pkey" PRIMARY KEY, btree (id) >>>>>>> "small_idx" gist (geomtext) >>>>>>> >>>>>>> >>>>>>> * >>>>>>> -- TABLE CONTENT* >>>>>>> bounds=# select id, st_astext( geomtext ) from small_tris; >>>>>>> id | st_astext >>>>>>> +--- >>>>>>> 0 | POLYGON Z ((10 10 805,20 10 809,10 20 814,10 10 805)) >>>>>>> 1 | POLYGON Z ((10 20 814,20 10 809,20 20 819,10 20 814)) >>>>>>> 2 | POLYGON Z ((20 20 819,30 10 809,30 20 812,20 20 819)) >>>>>>> 3 | POLYGON Z ((30 10 809,20 20 819,20 10 809,30 10 809)) >>>>>>> 4 | POLYGON Z ((30 20 812,30 10 809,40 10 826,30 20 812)) >>>>>>> 5 | POLYGON Z ((20 20 819,30 20 812,20 30 827,20 20 819)) >>>>>>> 6 | POLYGON Z ((10 20 814,20 20 819,20 30 827,10 20 814)) >>>>>>> 7 | POLYGON Z ((10 40 805,10 30 814,20 30 827,10 40 805)) >>>>>>> 8 | POLYGON Z ((10 50 804,10 40 805,20 40 807,10 50 804)) >>>>>>> 9 | POLYGON Z ((20 30 827,20 40 807,10 40 805,20 30 827)) >>>>>>> 10 | POLYGON Z ((30 30 817,20 40 807,20 30 827,30 30 817)) >>>>>>> 11 | POLYGON Z ((30 40 816,20 50 809,20 40 807,30 40 816)) >>>>>>> 12 | POLYGON Z ((20 40 807,20 50 809,10 50 804,20 40 807)) >>>>>>> 13 | POLYGON Z ((10 30 814,10 20 814,20 30 827,10 30 814)) >>>>>>> 14 | POLYGON Z ((30 30 817,20 30 827,30 20 812,30 30 817)) >>>>>>> 15 | POLYGON Z ((30 20 812,40 20 823,30 30 817,30 20 812)) >>>>>>> 16 | POLYGON Z ((40 20 823,30 20 812,40 10 826,40 20 823)) >>>>>>> 17 | POLYGON Z ((40 20 823,40 10 826,50 10 803,40 20 823)) >>>>>>> 18 | POLYGON Z ((30 30 817,40 20 823,40 30 814,30 30 817)) >>>>>>> 19 | POLYGON Z ((40 20 823,50 20 808,40 30 814,40 20 823)) >>>>>>> 20 | POLYGON Z ((50 20 808,40 20 823,50 10 803,50 20 808)) >>>>>>> 21 | POLYGON Z ((40 30 814,50 20 808,50 30 816,40 30 814)) >>>>>>> 22 | POLYGON Z ((30 30 817,40 30 814,40 40 800,30 30 817)) >>>>>>> 23 | POLYGON Z ((30 40 816,40 40 800,30 50 813,30 40 816)) >>>>>>> 24 | POLYGON Z ((30 40 816,30 30 817,40 40 800,30 40 816)) >>>>>>> 25 | POLYGON Z ((30 50 813,40 40 800,40 50 817,30 50 813)) >>>>>>> 26 | POLYGON Z ((30 40 816,30 50 813,20 50 809,30 40 816)) >>>>>>> 27 | POLYGON Z ((50 40 821,40 50 817,40 40 800,50 40 821)) >>>>>>> 28 | POLYGON Z ((50 50 809,40 50 817,50 40 821,50 50 809)) >>>>>>> 29 | POLYGON Z ((50 40 821,40 40 800,50 30 816,50 40 821)) >>>>>>> 30 | POLYGON Z ((40 30 814,50 30 816,40 40 800,40 30 814)) >>>>>>> 31 | POLYGON Z ((30 30 817,30 40 816,20 40 807,30 30 817)) >>>>>>> (32 rows) >>>>>>> >>>>>>> select st_astext(geomtext) from vfaces; >>>>>>> >>>>>>> st_astext >>>>>>> >>>>>>> --- >>>>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 >>>>>>> 828,51 35.2 828,51 17.8 799)) >>>>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 799,51 51 799,51 17.8 799)) >>>>>>> POLYGON Z ((51 35.2 828,19.4 51 828,51 51 828,51 35.2 828)) >>>>>>> POLYGON Z ((9 9 799,9 38.8 799,51 17.8 799,51 9 799,9 9 799)) >>>>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 >>>>>>> 828,51 35.2 828,51 17.8 799)) >>>>>>> POLYGON Z ((9 9 828,9 51 828,19.4 51 828,51 35.2 828,51 9 828,9 9 >>>>>>> 828)) >>>>>>> (6 rows) >>>>>>> >>>>>>> *select distinct a.id >>>>>>> from small_tris a, vfaces b >>>>>>> where st_intersects (a.geomtext, b.geomtext) >>>>>>> order by a.id;* >>>>>>> id >>>>>>> >>>>>>> 0 >>>>>>> 1 >>>>>>> 2 >>>>>>> 3 >>>>>>> 4 >>>>>>> 5 >>>>>>> 6 >>>>>>> 7 >>>>>>> 8 >>>>>>> 9 >>>>>>> 10 >>>>>>> 11 >>>>>>> 12 >>>>>>> 13 >>>>>>> 14 >>>>>>> 15 >>>>>>> 16 >>>>>>> 17 >>>>>>> 18 >>>>>>> 19 >>>>>>> 20 >>>>>>> 21 >>>>>>> 22 >>>>>>> 23 >>>>>>> 24 >>>>>>> 25 >>>>>>> 26 >>>>>>> 27 >>>>>>> 28 >>>>>>> 29 >>>>>>> 30 >>>>>>> 31 >>>>>>> (32 rows) >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> ___ >>>>>> 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 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with 3D polygon intersection test
gt;>>>> -- TABLE CONTENT* >>>>>> bounds=# select id, st_astext( geomtext ) from small_tris; >>>>>> id | st_astext >>>>>> +--- >>>>>> 0 | POLYGON Z ((10 10 805,20 10 809,10 20 814,10 10 805)) >>>>>> 1 | POLYGON Z ((10 20 814,20 10 809,20 20 819,10 20 814)) >>>>>> 2 | POLYGON Z ((20 20 819,30 10 809,30 20 812,20 20 819)) >>>>>> 3 | POLYGON Z ((30 10 809,20 20 819,20 10 809,30 10 809)) >>>>>> 4 | POLYGON Z ((30 20 812,30 10 809,40 10 826,30 20 812)) >>>>>> 5 | POLYGON Z ((20 20 819,30 20 812,20 30 827,20 20 819)) >>>>>> 6 | POLYGON Z ((10 20 814,20 20 819,20 30 827,10 20 814)) >>>>>> 7 | POLYGON Z ((10 40 805,10 30 814,20 30 827,10 40 805)) >>>>>> 8 | POLYGON Z ((10 50 804,10 40 805,20 40 807,10 50 804)) >>>>>> 9 | POLYGON Z ((20 30 827,20 40 807,10 40 805,20 30 827)) >>>>>> 10 | POLYGON Z ((30 30 817,20 40 807,20 30 827,30 30 817)) >>>>>> 11 | POLYGON Z ((30 40 816,20 50 809,20 40 807,30 40 816)) >>>>>> 12 | POLYGON Z ((20 40 807,20 50 809,10 50 804,20 40 807)) >>>>>> 13 | POLYGON Z ((10 30 814,10 20 814,20 30 827,10 30 814)) >>>>>> 14 | POLYGON Z ((30 30 817,20 30 827,30 20 812,30 30 817)) >>>>>> 15 | POLYGON Z ((30 20 812,40 20 823,30 30 817,30 20 812)) >>>>>> 16 | POLYGON Z ((40 20 823,30 20 812,40 10 826,40 20 823)) >>>>>> 17 | POLYGON Z ((40 20 823,40 10 826,50 10 803,40 20 823)) >>>>>> 18 | POLYGON Z ((30 30 817,40 20 823,40 30 814,30 30 817)) >>>>>> 19 | POLYGON Z ((40 20 823,50 20 808,40 30 814,40 20 823)) >>>>>> 20 | POLYGON Z ((50 20 808,40 20 823,50 10 803,50 20 808)) >>>>>> 21 | POLYGON Z ((40 30 814,50 20 808,50 30 816,40 30 814)) >>>>>> 22 | POLYGON Z ((30 30 817,40 30 814,40 40 800,30 30 817)) >>>>>> 23 | POLYGON Z ((30 40 816,40 40 800,30 50 813,30 40 816)) >>>>>> 24 | POLYGON Z ((30 40 816,30 30 817,40 40 800,30 40 816)) >>>>>> 25 | POLYGON Z ((30 50 813,40 40 800,40 50 817,30 50 813)) >>>>>> 26 | POLYGON Z ((30 40 816,30 50 813,20 50 809,30 40 816)) >>>>>> 27 | POLYGON Z ((50 40 821,40 50 817,40 40 800,50 40 821)) >>>>>> 28 | POLYGON Z ((50 50 809,40 50 817,50 40 821,50 50 809)) >>>>>> 29 | POLYGON Z ((50 40 821,40 40 800,50 30 816,50 40 821)) >>>>>> 30 | POLYGON Z ((40 30 814,50 30 816,40 40 800,40 30 814)) >>>>>> 31 | POLYGON Z ((30 30 817,30 40 816,20 40 807,30 30 817)) >>>>>> (32 rows) >>>>>> >>>>>> select st_astext(geomtext) from vfaces; >>>>>> >>>>>> st_astext >>>>>> >>>>>> --- >>>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 >>>>>> 828,51 35.2 828,51 17.8 799)) >>>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 799,51 51 799,51 17.8 799)) >>>>>> POLYGON Z ((51 35.2 828,19.4 51 828,51 51 828,51 35.2 828)) >>>>>> POLYGON Z ((9 9 799,9 38.8 799,51 17.8 799,51 9 799,9 9 799)) >>>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 >>>>>> 828,51 35.2 828,51 17.8 799)) >>>>>> POLYGON Z ((9 9 828,9 51 828,19.4 51 828,51 35.2 828,51 9 828,9 9 >>>>>> 828)) >>>>>> (6 rows) >>>>>> >>>>>> *select distinct a.id >>>>>> from small_tris a, vfaces b >>>>>> where st_intersects (a.geomtext, b.geomtext) >>>>>> order by a.id;* >>>>>> id >>>>>> >>>>>> 0 >>>>>> 1 >>>>>> 2 >>>>>> 3 >>>>>> 4 >>>>>> 5 >>>>>> 6 >>>>>> 7 >>>>>> 8 >>>>>> 9 >>>>>> 10 >>>>>> 11 >>>>>> 12 >>>>>> 13 >>>>>> 14 >>>>>> 15 >>>>>> 16 >>>>>> 17 >>>>>> 18 >>>>>> 19 >>>>>> 20 >>>>>> 21 >>>>>> 22 >>>>>> 23 >>>>>> 24 >>>>>> 25 >>>>>> 26 >>>>>> 27 >>>>>> 28 >>>>>> 29 >>>>>> 30 >>>>>> 31 >>>>>> (32 rows) >>>>>> >>>>>> >>>>>> >>>>> >>>>> ___ >>>>> 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 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with 3D polygon intersection test
)) >>>>> 3 | POLYGON Z ((30 10 809,20 20 819,20 10 809,30 10 809)) >>>>> 4 | POLYGON Z ((30 20 812,30 10 809,40 10 826,30 20 812)) >>>>> 5 | POLYGON Z ((20 20 819,30 20 812,20 30 827,20 20 819)) >>>>> 6 | POLYGON Z ((10 20 814,20 20 819,20 30 827,10 20 814)) >>>>> 7 | POLYGON Z ((10 40 805,10 30 814,20 30 827,10 40 805)) >>>>> 8 | POLYGON Z ((10 50 804,10 40 805,20 40 807,10 50 804)) >>>>> 9 | POLYGON Z ((20 30 827,20 40 807,10 40 805,20 30 827)) >>>>> 10 | POLYGON Z ((30 30 817,20 40 807,20 30 827,30 30 817)) >>>>> 11 | POLYGON Z ((30 40 816,20 50 809,20 40 807,30 40 816)) >>>>> 12 | POLYGON Z ((20 40 807,20 50 809,10 50 804,20 40 807)) >>>>> 13 | POLYGON Z ((10 30 814,10 20 814,20 30 827,10 30 814)) >>>>> 14 | POLYGON Z ((30 30 817,20 30 827,30 20 812,30 30 817)) >>>>> 15 | POLYGON Z ((30 20 812,40 20 823,30 30 817,30 20 812)) >>>>> 16 | POLYGON Z ((40 20 823,30 20 812,40 10 826,40 20 823)) >>>>> 17 | POLYGON Z ((40 20 823,40 10 826,50 10 803,40 20 823)) >>>>> 18 | POLYGON Z ((30 30 817,40 20 823,40 30 814,30 30 817)) >>>>> 19 | POLYGON Z ((40 20 823,50 20 808,40 30 814,40 20 823)) >>>>> 20 | POLYGON Z ((50 20 808,40 20 823,50 10 803,50 20 808)) >>>>> 21 | POLYGON Z ((40 30 814,50 20 808,50 30 816,40 30 814)) >>>>> 22 | POLYGON Z ((30 30 817,40 30 814,40 40 800,30 30 817)) >>>>> 23 | POLYGON Z ((30 40 816,40 40 800,30 50 813,30 40 816)) >>>>> 24 | POLYGON Z ((30 40 816,30 30 817,40 40 800,30 40 816)) >>>>> 25 | POLYGON Z ((30 50 813,40 40 800,40 50 817,30 50 813)) >>>>> 26 | POLYGON Z ((30 40 816,30 50 813,20 50 809,30 40 816)) >>>>> 27 | POLYGON Z ((50 40 821,40 50 817,40 40 800,50 40 821)) >>>>> 28 | POLYGON Z ((50 50 809,40 50 817,50 40 821,50 50 809)) >>>>> 29 | POLYGON Z ((50 40 821,40 40 800,50 30 816,50 40 821)) >>>>> 30 | POLYGON Z ((40 30 814,50 30 816,40 40 800,40 30 814)) >>>>> 31 | POLYGON Z ((30 30 817,30 40 816,20 40 807,30 30 817)) >>>>> (32 rows) >>>>> >>>>> select st_astext(geomtext) from vfaces; >>>>> >>>>> st_astext >>>>> >>>>> ------- >>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 >>>>> 828,51 35.2 828,51 17.8 799)) >>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 799,51 51 799,51 17.8 799)) >>>>> POLYGON Z ((51 35.2 828,19.4 51 828,51 51 828,51 35.2 828)) >>>>> POLYGON Z ((9 9 799,9 38.8 799,51 17.8 799,51 9 799,9 9 799)) >>>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 >>>>> 828,51 35.2 828,51 17.8 799)) >>>>> POLYGON Z ((9 9 828,9 51 828,19.4 51 828,51 35.2 828,51 9 828,9 9 >>>>> 828)) >>>>> (6 rows) >>>>> >>>>> *select distinct a.id >>>>> from small_tris a, vfaces b >>>>> where st_intersects (a.geomtext, b.geomtext) >>>>> order by a.id;* >>>>> id >>>>> >>>>> 0 >>>>> 1 >>>>> 2 >>>>> 3 >>>>> 4 >>>>> 5 >>>>> 6 >>>>> 7 >>>>> 8 >>>>> 9 >>>>> 10 >>>>> 11 >>>>> 12 >>>>> 13 >>>>> 14 >>>>> 15 >>>>> 16 >>>>> 17 >>>>> 18 >>>>> 19 >>>>> 20 >>>>> 21 >>>>> 22 >>>>> 23 >>>>> 24 >>>>> 25 >>>>> 26 >>>>> 27 >>>>> 28 >>>>> 29 >>>>> 30 >>>>> 31 >>>>> (32 rows) >>>>> >>>>> >>>>> >>>> >>>> ___ >>>> 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 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] Problem with 3D polygon intersection test
807,30 40 816)) >>>> 12 | POLYGON Z ((20 40 807,20 50 809,10 50 804,20 40 807)) >>>> 13 | POLYGON Z ((10 30 814,10 20 814,20 30 827,10 30 814)) >>>> 14 | POLYGON Z ((30 30 817,20 30 827,30 20 812,30 30 817)) >>>> 15 | POLYGON Z ((30 20 812,40 20 823,30 30 817,30 20 812)) >>>> 16 | POLYGON Z ((40 20 823,30 20 812,40 10 826,40 20 823)) >>>> 17 | POLYGON Z ((40 20 823,40 10 826,50 10 803,40 20 823)) >>>> 18 | POLYGON Z ((30 30 817,40 20 823,40 30 814,30 30 817)) >>>> 19 | POLYGON Z ((40 20 823,50 20 808,40 30 814,40 20 823)) >>>> 20 | POLYGON Z ((50 20 808,40 20 823,50 10 803,50 20 808)) >>>> 21 | POLYGON Z ((40 30 814,50 20 808,50 30 816,40 30 814)) >>>> 22 | POLYGON Z ((30 30 817,40 30 814,40 40 800,30 30 817)) >>>> 23 | POLYGON Z ((30 40 816,40 40 800,30 50 813,30 40 816)) >>>> 24 | POLYGON Z ((30 40 816,30 30 817,40 40 800,30 40 816)) >>>> 25 | POLYGON Z ((30 50 813,40 40 800,40 50 817,30 50 813)) >>>> 26 | POLYGON Z ((30 40 816,30 50 813,20 50 809,30 40 816)) >>>> 27 | POLYGON Z ((50 40 821,40 50 817,40 40 800,50 40 821)) >>>> 28 | POLYGON Z ((50 50 809,40 50 817,50 40 821,50 50 809)) >>>> 29 | POLYGON Z ((50 40 821,40 40 800,50 30 816,50 40 821)) >>>> 30 | POLYGON Z ((40 30 814,50 30 816,40 40 800,40 30 814)) >>>> 31 | POLYGON Z ((30 30 817,30 40 816,20 40 807,30 30 817)) >>>> (32 rows) >>>> >>>> select st_astext(geomtext) from vfaces; >>>> >>>> st_astext >>>> >>>> ------- >>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 >>>> 35.2 828,51 17.8 799)) >>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 799,51 51 799,51 17.8 799)) >>>> POLYGON Z ((51 35.2 828,19.4 51 828,51 51 828,51 35.2 828)) >>>> POLYGON Z ((9 9 799,9 38.8 799,51 17.8 799,51 9 799,9 9 799)) >>>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 >>>> 35.2 828,51 17.8 799)) >>>> POLYGON Z ((9 9 828,9 51 828,19.4 51 828,51 35.2 828,51 9 828,9 9 828)) >>>> (6 rows) >>>> >>>> *select distinct a.id >>>> from small_tris a, vfaces b >>>> where st_intersects (a.geomtext, b.geomtext) >>>> order by a.id;* >>>> id >>>> >>>> 0 >>>> 1 >>>> 2 >>>> 3 >>>> 4 >>>> 5 >>>> 6 >>>> 7 >>>> 8 >>>> 9 >>>> 10 >>>> 11 >>>> 12 >>>> 13 >>>> 14 >>>> 15 >>>> 16 >>>> 17 >>>> 18 >>>> 19 >>>> 20 >>>> 21 >>>> 22 >>>> 23 >>>> 24 >>>> 25 >>>> 26 >>>> 27 >>>> 28 >>>> 29 >>>> 30 >>>> 31 >>>> (32 rows) >>>> >>>> >>>> >>> >>> ___ >>> 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with 3D polygon intersection test
20 823)) >>> 20 | POLYGON Z ((50 20 808,40 20 823,50 10 803,50 20 808)) >>> 21 | POLYGON Z ((40 30 814,50 20 808,50 30 816,40 30 814)) >>> 22 | POLYGON Z ((30 30 817,40 30 814,40 40 800,30 30 817)) >>> 23 | POLYGON Z ((30 40 816,40 40 800,30 50 813,30 40 816)) >>> 24 | POLYGON Z ((30 40 816,30 30 817,40 40 800,30 40 816)) >>> 25 | POLYGON Z ((30 50 813,40 40 800,40 50 817,30 50 813)) >>> 26 | POLYGON Z ((30 40 816,30 50 813,20 50 809,30 40 816)) >>> 27 | POLYGON Z ((50 40 821,40 50 817,40 40 800,50 40 821)) >>> 28 | POLYGON Z ((50 50 809,40 50 817,50 40 821,50 50 809)) >>> 29 | POLYGON Z ((50 40 821,40 40 800,50 30 816,50 40 821)) >>> 30 | POLYGON Z ((40 30 814,50 30 816,40 40 800,40 30 814)) >>> 31 | POLYGON Z ((30 30 817,30 40 816,20 40 807,30 30 817)) >>> (32 rows) >>> >>> select st_astext(geomtext) from vfaces; >>> >>> st_astext >>> >>> ----------- >>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 >>> 35.2 828,51 17.8 799)) >>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 799,51 51 799,51 17.8 799)) >>> POLYGON Z ((51 35.2 828,19.4 51 828,51 51 828,51 35.2 828)) >>> POLYGON Z ((9 9 799,9 38.8 799,51 17.8 799,51 9 799,9 9 799)) >>> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 >>> 35.2 828,51 17.8 799)) >>> POLYGON Z ((9 9 828,9 51 828,19.4 51 828,51 35.2 828,51 9 828,9 9 828)) >>> (6 rows) >>> >>> *select distinct a.id >>> from small_tris a, vfaces b >>> where st_intersects (a.geomtext, b.geomtext) >>> order by a.id;* >>> id >>> >>> 0 >>> 1 >>> 2 >>> 3 >>> 4 >>> 5 >>> 6 >>> 7 >>> 8 >>> 9 >>> 10 >>> 11 >>> 12 >>> 13 >>> 14 >>> 15 >>> 16 >>> 17 >>> 18 >>> 19 >>> 20 >>> 21 >>> 22 >>> 23 >>> 24 >>> 25 >>> 26 >>> 27 >>> 28 >>> 29 >>> 30 >>> 31 >>> (32 rows) >>> >>> >>> >> >> ___ >> 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with 3D polygon intersection test
Z ((50 40 821,40 40 800,50 30 816,50 40 821)) >> 30 | POLYGON Z ((40 30 814,50 30 816,40 40 800,40 30 814)) >> 31 | POLYGON Z ((30 30 817,30 40 816,20 40 807,30 30 817)) >> (32 rows) >> >> select st_astext(geomtext) from vfaces; >> >> st_astext >> >> --- >> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 >> 35.2 828,51 17.8 799)) >> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 799,51 51 799,51 17.8 799)) >> POLYGON Z ((51 35.2 828,19.4 51 828,51 51 828,51 35.2 828)) >> POLYGON Z ((9 9 799,9 38.8 799,51 17.8 799,51 9 799,9 9 799)) >> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 >> 35.2 828,51 17.8 799)) >> POLYGON Z ((9 9 828,9 51 828,19.4 51 828,51 35.2 828,51 9 828,9 9 828)) >> (6 rows) >> >> *select distinct a.id >> from small_tris a, vfaces b >> where st_intersects (a.geomtext, b.geomtext) >> order by a.id;* >> id >> >> 0 >> 1 >> 2 >> 3 >> 4 >> 5 >> 6 >> 7 >> 8 >> 9 >> 10 >> 11 >> 12 >> 13 >> 14 >> 15 >> 16 >> 17 >> 18 >> 19 >> 20 >> 21 >> 22 >> 23 >> 24 >> 25 >> 26 >> 27 >> 28 >> 29 >> 30 >> 31 >> (32 rows) >> >> >> > > ___ > 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] Problem with 3D polygon intersection test
20 812,40 20 823,30 30 817,30 20 812)) >> 16 | POLYGON Z ((40 20 823,30 20 812,40 10 826,40 20 823)) >> 17 | POLYGON Z ((40 20 823,40 10 826,50 10 803,40 20 823)) >> 18 | POLYGON Z ((30 30 817,40 20 823,40 30 814,30 30 817)) >> 19 | POLYGON Z ((40 20 823,50 20 808,40 30 814,40 20 823)) >> 20 | POLYGON Z ((50 20 808,40 20 823,50 10 803,50 20 808)) >> 21 | POLYGON Z ((40 30 814,50 20 808,50 30 816,40 30 814)) >> 22 | POLYGON Z ((30 30 817,40 30 814,40 40 800,30 30 817)) >> 23 | POLYGON Z ((30 40 816,40 40 800,30 50 813,30 40 816)) >> 24 | POLYGON Z ((30 40 816,30 30 817,40 40 800,30 40 816)) >> 25 | POLYGON Z ((30 50 813,40 40 800,40 50 817,30 50 813)) >> 26 | POLYGON Z ((30 40 816,30 50 813,20 50 809,30 40 816)) >> 27 | POLYGON Z ((50 40 821,40 50 817,40 40 800,50 40 821)) >> 28 | POLYGON Z ((50 50 809,40 50 817,50 40 821,50 50 809)) >> 29 | POLYGON Z ((50 40 821,40 40 800,50 30 816,50 40 821)) >> 30 | POLYGON Z ((40 30 814,50 30 816,40 40 800,40 30 814)) >> 31 | POLYGON Z ((30 30 817,30 40 816,20 40 807,30 30 817)) >> (32 rows) >> >> select st_astext(geomtext) from vfaces; >> >> st_astext >> >> --- >> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 >> 35.2 828,51 17.8 799)) >> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 799,51 51 799,51 17.8 799)) >> POLYGON Z ((51 35.2 828,19.4 51 828,51 51 828,51 35.2 828)) >> POLYGON Z ((9 9 799,9 38.8 799,51 17.8 799,51 9 799,9 9 799)) >> POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 >> 35.2 828,51 17.8 799)) >> POLYGON Z ((9 9 828,9 51 828,19.4 51 828,51 35.2 828,51 9 828,9 9 828)) >> (6 rows) >> >> *select distinct a.id >> from small_tris a, vfaces b >> where st_intersects (a.geomtext, b.geomtext) >> order by a.id;* >> id >> >> 0 >> 1 >> 2 >> 3 >> 4 >> 5 >> 6 >> 7 >> 8 >> 9 >> 10 >> 11 >> 12 >> 13 >> 14 >> 15 >> 16 >> 17 >> 18 >> 19 >> 20 >> 21 >> 22 >> 23 >> 24 >> 25 >> 26 >> 27 >> 28 >> 29 >> 30 >> 31 >> (32 rows) >> >> >> > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with 3D polygon intersection test
51 35.2 828,19.4 51 828,51 51 828,51 35.2 828)) > POLYGON Z ((9 9 799,9 38.8 799,51 17.8 799,51 9 799,9 9 799)) > POLYGON Z ((51 17.8 799,9 38.8 799,9 51 819.333,19.4 51 828,51 > 35.2 828,51 17.8 799)) > POLYGON Z ((9 9 828,9 51 828,19.4 51 828,51 35.2 828,51 9 828,9 9 828)) > (6 rows) > > *select distinct a.id > from small_tris a, vfaces b > where st_intersects (a.geomtext, b.geomtext) > order by a.id;* > id > > 0 > 1 > 2 > 3 > 4 > 5 > 6 > 7 > 8 > 9 > 10 > 11 > 12 > 13 > 14 > 15 > 16 > 17 > 18 > 19 > 20 > 21 > 22 > 23 > 24 > 25 > 26 > 27 > 28 > 29 > 30 > 31 > (32 rows) > > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
James-
another optionis to use rasdaman [1] which runs on top of PostgreSQL. It is
genuinely multi-dimensional, so it can handle image timeseries as one compact
object and access/process it efficiently. Addtionally, you can tune storage
layout for time series access patterns. We have just done this for the German
Metoffice for a climate change detection use case using 50 years of climate data
(to be presented at ACM BIGSPATIAL next week); change detection is done in a few
100ms for any point in Germany on this cube.
-Peter
[1] www.rasdaman.org
On 10/29/2012 11:50 PM, Bborie Park wrote:
James,
I use PostGIS raster for a similar purpose (model outputs) though my
model outputs are for a specific day (average temperature for a specific
date). So, one raster with one band per day per variable. I could
combine a year's worth of bands into one raster but I decided against that.
If you've got a large number of bands (100s or more), you may want to
consider having the rasters be out-of-db. Part of the problem is that
anything stored in PostgreSQL (in-db) is TOASTed so needs to be
deserialized (and probably decompressed). So, if the serialized raster
is big (more bands), the deTOASTing will take longer.
Another problem with your benchmark query is that the ST_Clip() is
running twice (for height and width).
If you're in the evaluation stage and you're compiling PostGIS yourself,
I'd recommend trying SVN -trunk (will become 2.1) as it has additional
capabilities and performance improvements. I'm already using -trunk in
production as I needed the new features (full disclosure: I wrote almost
the new features in -trunk).
-bborie
On 10/29/2012 03:32 PM, James Hiebert wrote:
Hi All,
I'm considering using PostGIS rasters for storage of raster data at my
organization and I'm looking for some advice (or perhaps a reality check). I
work for a region climate services provider and the vast majority of our data
(by volume, not necessarily complexity) are output from climate models. These
are generally a n-by-m raster with one band for each timestep. There could be
upwards of 36k to 72k timesteps for a typical model run. We have hundreds of
model runs.
So my question is, is it insane to be thinking of storing that many bands in a PostGIS
raster? Or more specifically, is this _not_ a use case for which PostGIS rasters were
designed? I notice that most of the examples in the docs and in "PostGIS In
Action" focus only on images and I can imagine that handling multispectral satellite
images as being more of the intended use case.
I did a little benchmarking of a typical use case of ours ("What's the average
temperature inside a some polygon, e.g. a river basin?"). I noticed that the run
time for doing a ST_Clip(raster, band, geometry) and ST_Intersects(raster, band,
geometry) appears to be super-linear even when doing it on just a single band. I ran the
following query:
SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast, the_geom)) FROM
basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) WHERE rid = (where
basins is table of river basins with one single polygon and bcsd is a table with a raster
column "rast").
for a set of rasters with increasing number of bands, and the time to run the
query is shown in the attached plot. Since the raster properties are
presumably shared across all the bands, it seems odd to me that run time would
increase. I would expect it to be _contant_ (with constant number of pixels),
but I suppose that that's my own ignorance as to how the PG type extensions
work?
Comments or explanations are welcome.
~James
_______
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Dr. Peter Baumann
- Professor of Computer Science, Jacobs University Bremen
www.faculty.jacobs-university.de/pbaumann
mail: p.baum...@jacobs-university.de
tel: +49-421-200-3178, fax: +49-421-200-493178
- Executive Director, rasdaman GmbH Bremen (HRB 26793)
www.rasdaman.com, mail: baum...@rasdaman.com
tel: 0800-rasdaman, fax: 0800-rasdafax, mobile: +49-173-5837882
"Si forte in alienas manus oberraverit hec peregrina epistola incertis ventis
dimissa, sed Deo commendata, precamur ut ei reddatur cui soli destinata, nec preripiat
quisquam non sibi parata." (mail disclaimer, AD 1083)
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Optimizing nearest neighbor search with conditions
Unless you can draw a set from the ordering that is large enough to always contain a result that passes your filters, I think you're out of luck. The reality is that the ordering operators are quite limited, there's all sorts of useful things one would like to do with them (table join on nearest, for example) that one cannot. P. On Mon, Oct 29, 2012 at 8:11 PM, Doug Cole wrote: > I recently updated a postgresql database to use postgis 2.0.1 - I am > trying to improve the performance of our nearest neighbor queries > using the new <-> operator, but ran into an issue. Our nearest > neighbor query also has a handful of other conditions beyond just > distance. For the majority of queries using <-> speeds up the queries > by an order of magnitude, unfortunately in the cases where the other > conditions can't be met for the query, it performance a sequential > scan of the entire index, taking a query that normally takes 20-30ms > take 7 seconds. > > I've tried several things to work around this: > * adding a bounding box similar to what we used with postgis 1.x - it > always uses the index with the bounding box, even when using the index > with the order by would be 10x faster > * increasing statistics value for the geometry column in case that > was causing the poor choice of index usage, this had no effect > * adding a distance constraint that can't be used for the index > (st_distance_sphere) - this reverts back to the full index scan, but > doesn't halt the full index scan in the worst case. It doesn't seem > able to recognize the fact that logically no further items in the > ordered index could match > > Is this a known limitation for nearest neighbor queries with postgis > or is there a known workaround for this kind of situation? Thanks in > advance for your help, > Doug > _______ > 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] Optimizing nearest neighbor search with conditions
I recently updated a postgresql database to use postgis 2.0.1 - I am trying to improve the performance of our nearest neighbor queries using the new <-> operator, but ran into an issue. Our nearest neighbor query also has a handful of other conditions beyond just distance. For the majority of queries using <-> speeds up the queries by an order of magnitude, unfortunately in the cases where the other conditions can't be met for the query, it performance a sequential scan of the entire index, taking a query that normally takes 20-30ms take 7 seconds. I've tried several things to work around this: * adding a bounding box similar to what we used with postgis 1.x - it always uses the index with the bounding box, even when using the index with the order by would be 10x faster * increasing statistics value for the geometry column in case that was causing the poor choice of index usage, this had no effect * adding a distance constraint that can't be used for the index (st_distance_sphere) - this reverts back to the full index scan, but doesn't halt the full index scan in the worst case. It doesn't seem able to recognize the fact that logically no further items in the ordered index could match Is this a known limitation for nearest neighbor queries with postgis or is there a known workaround for this kind of situation? Thanks in advance for your help, Doug _______________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
e pretty dramatically (see attached plot).
> >>> Since it improves by a lot more than a factor of two, I suspect maybe I'm
> >>> having some desktop scaling issues or something. I'll go ahead and
> >>> actually put this on our database server, try the trunk version, and go
> >>> from there. This is at least somewhat encouraging :) Thanks for the
> >>> suggestions.
> >>>
> >>> ~James
> >>>
> >>> On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote:
> >>>> James,
> >>>>
> >>>> I use PostGIS raster for a similar purpose (model outputs) though my
> >>>> model outputs are for a specific day (average temperature for a specific
> >>>> date). So, one raster with one band per day per variable. I could
> >>>> combine a year's worth of bands into one raster but I decided against
> >>>> that.
> >>>>
> >>>> If you've got a large number of bands (100s or more), you may want to
> >>>> consider having the rasters be out-of-db. Part of the problem is that
> >>>> anything stored in PostgreSQL (in-db) is TOASTed so needs to be
> >>>> deserialized (and probably decompressed). So, if the serialized raster
> >>>> is big (more bands), the deTOASTing will take longer.
> >>>>
> >>>> Another problem with your benchmark query is that the ST_Clip() is
> >>>> running twice (for height and width).
> >>>>
> >>>> If you're in the evaluation stage and you're compiling PostGIS yourself,
> >>>> I'd recommend trying SVN -trunk (will become 2.1) as it has additional
> >>>> capabilities and performance improvements. I'm already using -trunk in
> >>>> production as I needed the new features (full disclosure: I wrote almost
> >>>> the new features in -trunk).
> >>>>
> >>>> -bborie
> >>>>
> >>>> On 10/29/2012 03:32 PM, James Hiebert wrote:
> >>>>> Hi All,
> >>>>>
> >>>>> I'm considering using PostGIS rasters for storage of raster data at my
> >>>>> organization and I'm looking for some advice (or perhaps a reality
> >>>>> check). I work for a region climate services provider and the vast
> >>>>> majority of our data (by volume, not necessarily complexity) are output
> >>>>> from climate models. These are generally a n-by-m raster with one band
> >>>>> for each timestep. There could be upwards of 36k to 72k timesteps for
> >>>>> a typical model run. We have hundreds of model runs.
> >>>>>
> >>>>> So my question is, is it insane to be thinking of storing that many
> >>>>> bands in a PostGIS raster? Or more specifically, is this _not_ a use
> >>>>> case for which PostGIS rasters were designed? I notice that most of
> >>>>> the examples in the docs and in "PostGIS In Action" focus only on
> >>>>> images and I can imagine that handling multispectral satellite images
> >>>>> as being more of the intended use case.
> >>>>>
> >>>>> I did a little benchmarking of a typical use case of ours ("What's the
> >>>>> average temperature inside a some polygon, e.g. a river basin?"). I
> >>>>> noticed that the run time for doing a ST_Clip(raster, band, geometry)
> >>>>> and ST_Intersects(raster, band, geometry) appears to be super-linear
> >>>>> even when doing it on just a single band. I ran the following query:
> >>>>> SELECT rid, st_height(st_clip(rast, 1, the_geom)),
> >>>>> st_width(st_clip(rast, the_geom)) FROM basins INNER JOIN bcsd ON
> >>>>> ST_Intersects(rast, 1, the_geom) WHERE rid = (where basins is
> >>>>> table of river basins with one single polygon and bcsd is a table with
> >>>>> a raster column "rast").
> >>>>> for a set of rasters with increasing number of bands, and the time to
> >>>>> run the query is shown in the attached plot. Since the raster
> >>>>> properties are presumably shared across all the bands, it seems odd to
> >>>>> me that run time would increase. I would expect it to be _contant_
> >>>>> (with constant number of pixels), but I suppose that that's my own
> >>>>> ignorance as to how the PG type extensions work?
> >>>>>
> >>>>> Comments or explanations are welcome.
> >>>>>
> >>>>> ~James
> >
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
--
James Hiebert
Lead, Computational Support
Pacific Climate Impacts Consortium
http://www.pacificclimate.org
Room 112, University House 1, University of Victoria
PO Box 1700 Sta CSC, Victoria, BC V8V 2Y2
E-mail: hieb...@uvic.ca
Tel: (250) 472-4521
Fax: (250) 472-4830
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
ed (and probably decompressed). So, if the serialized raster
>>>> is big (more bands), the deTOASTing will take longer.
>>>>
>>>> Another problem with your benchmark query is that the ST_Clip() is
>>>> running twice (for height and width).
>>>>
>>>> If you're in the evaluation stage and you're compiling PostGIS yourself,
>>>> I'd recommend trying SVN -trunk (will become 2.1) as it has additional
>>>> capabilities and performance improvements. I'm already using -trunk in
>>>> production as I needed the new features (full disclosure: I wrote almost
>>>> the new features in -trunk).
>>>>
>>>> -bborie
>>>>
>>>> On 10/29/2012 03:32 PM, James Hiebert wrote:
>>>>> Hi All,
>>>>>
>>>>> I'm considering using PostGIS rasters for storage of raster data at my
>>>>> organization and I'm looking for some advice (or perhaps a reality
>>>>> check). I work for a region climate services provider and the vast
>>>>> majority of our data (by volume, not necessarily complexity) are output
>>>>> from climate models. These are generally a n-by-m raster with one band
>>>>> for each timestep. There could be upwards of 36k to 72k timesteps for a
>>>>> typical model run. We have hundreds of model runs.
>>>>>
>>>>> So my question is, is it insane to be thinking of storing that many bands
>>>>> in a PostGIS raster? Or more specifically, is this _not_ a use case for
>>>>> which PostGIS rasters were designed? I notice that most of the examples
>>>>> in the docs and in "PostGIS In Action" focus only on images and I can
>>>>> imagine that handling multispectral satellite images as being more of the
>>>>> intended use case.
>>>>>
>>>>> I did a little benchmarking of a typical use case of ours ("What's the
>>>>> average temperature inside a some polygon, e.g. a river basin?"). I
>>>>> noticed that the run time for doing a ST_Clip(raster, band, geometry) and
>>>>> ST_Intersects(raster, band, geometry) appears to be super-linear even
>>>>> when doing it on just a single band. I ran the following query:
>>>>> SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast,
>>>>> the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1,
>>>>> the_geom) WHERE rid = (where basins is table of river basins with
>>>>> one single polygon and bcsd is a table with a raster column "rast").
>>>>> for a set of rasters with increasing number of bands, and the time to run
>>>>> the query is shown in the attached plot. Since the raster properties are
>>>>> presumably shared across all the bands, it seems odd to me that run time
>>>>> would increase. I would expect it to be _contant_ (with constant number
>>>>> of pixels), but I suppose that that's my own ignorance as to how the PG
>>>>> type extensions work?
>>>>>
>>>>> Comments or explanations are welcome.
>>>>>
>>>>> ~James
>
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
te almost
> >> the new features in -trunk).
> >>
> >> -bborie
> >>
> >> On 10/29/2012 03:32 PM, James Hiebert wrote:
> >>> Hi All,
> >>>
> >>> I'm considering using PostGIS rasters for storage of raster data at my
> >>> organization and I'm looking for some advice (or perhaps a reality
> >>> check). I work for a region climate services provider and the vast
> >>> majority of our data (by volume, not necessarily complexity) are output
> >>> from climate models. These are generally a n-by-m raster with one band
> >>> for each timestep. There could be upwards of 36k to 72k timesteps for a
> >>> typical model run. We have hundreds of model runs.
> >>>
> >>> So my question is, is it insane to be thinking of storing that many bands
> >>> in a PostGIS raster? Or more specifically, is this _not_ a use case for
> >>> which PostGIS rasters were designed? I notice that most of the examples
> >>> in the docs and in "PostGIS In Action" focus only on images and I can
> >>> imagine that handling multispectral satellite images as being more of the
> >>> intended use case.
> >>>
> >>> I did a little benchmarking of a typical use case of ours ("What's the
> >>> average temperature inside a some polygon, e.g. a river basin?"). I
> >>> noticed that the run time for doing a ST_Clip(raster, band, geometry) and
> >>> ST_Intersects(raster, band, geometry) appears to be super-linear even
> >>> when doing it on just a single band. I ran the following query:
> >>> SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast,
> >>> the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1,
> >>> the_geom) WHERE rid = (where basins is table of river basins with
> >>> one single polygon and bcsd is a table with a raster column "rast").
> >>> for a set of rasters with increasing number of bands, and the time to run
> >>> the query is shown in the attached plot. Since the raster properties are
> >>> presumably shared across all the bands, it seems odd to me that run time
> >>> would increase. I would expect it to be _contant_ (with constant number
> >>> of pixels), but I suppose that that's my own ignorance as to how the PG
> >>> type extensions work?
> >>>
> >>> Comments or explanations are welcome.
> >>>
> >>> ~James
--
James Hiebert
Lead, Computational Support
Pacific Climate Impacts Consortium
http://www.pacificclimate.org
Room 112, University House 1, University of Victoria
PO Box 1700 Sta CSC, Victoria, BC V8V 2Y2
E-mail: hieb...@uvic.ca
Tel: (250) 472-4521
Fax: (250) 472-4830
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
, band, geometry) and >>> ST_Intersects(raster, band, geometry) appears to be super-linear even when >>> doing it on just a single band. I ran the following query: >>> SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast, >>> the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom) >>> WHERE rid = (where basins is table of river basins with one single >>> polygon and bcsd is a table with a raster column "rast"). >>> for a set of rasters with increasing number of bands, and the time to run >>> the query is shown in the attached plot. Since the raster properties are >>> presumably shared across all the bands, it seems odd to me that run time >>> would increase. I would expect it to be _contant_ (with constant number of >>> pixels), but I suppose that that's my own ignorance as to how the PG type >>> extensions work? >>> >>> Comments or explanations are welcome. >>> >>> ~James > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is this a perverse use-case for raster types?
> If you've got a large number of bands (100s or more), you may want to
> consider having the rasters be out-of-db.
I had considered that (better, actually, than duplicating our data, right?),
but was finding that st_intersects wasn't yet implemented for out of db
storage. Looking through the trunk code, though, it appears that maybe you've
gone ahead and implemented that since 2.0.1? If so, great! ST_PixelAsPoints()
is another good reason for me to seriously consider working out of trunk...
> Part of the problem is that
> anything stored in PostgreSQL (in-db) is TOASTed so needs to be
> deserialized (and probably decompressed). So, if the serialized raster
> is big (more bands), the deTOASTing will take longer.
Thanks; good to know.
> Another problem with your benchmark query is that the ST_Clip() is
> running twice (for height and width).
Ah, that changes the picture pretty dramatically (see attached plot). Since it
improves by a lot more than a factor of two, I suspect maybe I'm having some
desktop scaling issues or something. I'll go ahead and actually put this on
our database server, try the trunk version, and go from there. This is at
least somewhat encouraging :) Thanks for the suggestions.
~James
On Mon, Oct 29, 2012 at 03:50:04PM -0700, Bborie Park wrote:
> James,
>
> I use PostGIS raster for a similar purpose (model outputs) though my
> model outputs are for a specific day (average temperature for a specific
> date). So, one raster with one band per day per variable. I could
> combine a year's worth of bands into one raster but I decided against that.
>
> If you've got a large number of bands (100s or more), you may want to
> consider having the rasters be out-of-db. Part of the problem is that
> anything stored in PostgreSQL (in-db) is TOASTed so needs to be
> deserialized (and probably decompressed). So, if the serialized raster
> is big (more bands), the deTOASTing will take longer.
>
> Another problem with your benchmark query is that the ST_Clip() is
> running twice (for height and width).
>
> If you're in the evaluation stage and you're compiling PostGIS yourself,
> I'd recommend trying SVN -trunk (will become 2.1) as it has additional
> capabilities and performance improvements. I'm already using -trunk in
> production as I needed the new features (full disclosure: I wrote almost
> the new features in -trunk).
>
> -bborie
>
> On 10/29/2012 03:32 PM, James Hiebert wrote:
> > Hi All,
> >
> > I'm considering using PostGIS rasters for storage of raster data at my
> > organization and I'm looking for some advice (or perhaps a reality check).
> > I work for a region climate services provider and the vast majority of our
> > data (by volume, not necessarily complexity) are output from climate
> > models. These are generally a n-by-m raster with one band for each
> > timestep. There could be upwards of 36k to 72k timesteps for a typical
> > model run. We have hundreds of model runs.
> >
> > So my question is, is it insane to be thinking of storing that many bands
> > in a PostGIS raster? Or more specifically, is this _not_ a use case for
> > which PostGIS rasters were designed? I notice that most of the examples in
> > the docs and in "PostGIS In Action" focus only on images and I can imagine
> > that handling multispectral satellite images as being more of the intended
> > use case.
> >
> > I did a little benchmarking of a typical use case of ours ("What's the
> > average temperature inside a some polygon, e.g. a river basin?"). I
> > noticed that the run time for doing a ST_Clip(raster, band, geometry) and
> > ST_Intersects(raster, band, geometry) appears to be super-linear even when
> > doing it on just a single band. I ran the following query:
> > SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast,
> > the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom)
> > WHERE rid = (where basins is table of river basins with one single
> > polygon and bcsd is a table with a raster column "rast").
> > for a set of rasters with increasing number of bands, and the time to run
> > the query is shown in the attached plot. Since the raster properties are
> > presumably shared across all the bands, it seems odd to me that run time
> > would increase. I would expect it to be _contant_ (with constant number of
> > pixels), but I suppose that that's my own ignorance as to how the PG type
> > extensions work?
> >
> > Comments or explanations are welcome.
> >
> > ~James
--
James Hi
Re: [postgis-users] Is this a perverse use-case for raster types?
James,
I use PostGIS raster for a similar purpose (model outputs) though my
model outputs are for a specific day (average temperature for a specific
date). So, one raster with one band per day per variable. I could
combine a year's worth of bands into one raster but I decided against that.
If you've got a large number of bands (100s or more), you may want to
consider having the rasters be out-of-db. Part of the problem is that
anything stored in PostgreSQL (in-db) is TOASTed so needs to be
deserialized (and probably decompressed). So, if the serialized raster
is big (more bands), the deTOASTing will take longer.
Another problem with your benchmark query is that the ST_Clip() is
running twice (for height and width).
If you're in the evaluation stage and you're compiling PostGIS yourself,
I'd recommend trying SVN -trunk (will become 2.1) as it has additional
capabilities and performance improvements. I'm already using -trunk in
production as I needed the new features (full disclosure: I wrote almost
the new features in -trunk).
-bborie
On 10/29/2012 03:32 PM, James Hiebert wrote:
> Hi All,
>
> I'm considering using PostGIS rasters for storage of raster data at my
> organization and I'm looking for some advice (or perhaps a reality check). I
> work for a region climate services provider and the vast majority of our data
> (by volume, not necessarily complexity) are output from climate models.
> These are generally a n-by-m raster with one band for each timestep. There
> could be upwards of 36k to 72k timesteps for a typical model run. We have
> hundreds of model runs.
>
> So my question is, is it insane to be thinking of storing that many bands in
> a PostGIS raster? Or more specifically, is this _not_ a use case for which
> PostGIS rasters were designed? I notice that most of the examples in the
> docs and in "PostGIS In Action" focus only on images and I can imagine that
> handling multispectral satellite images as being more of the intended use
> case.
>
> I did a little benchmarking of a typical use case of ours ("What's the
> average temperature inside a some polygon, e.g. a river basin?"). I noticed
> that the run time for doing a ST_Clip(raster, band, geometry) and
> ST_Intersects(raster, band, geometry) appears to be super-linear even when
> doing it on just a single band. I ran the following query:
> SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast,
> the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom)
> WHERE rid = (where basins is table of river basins with one single
> polygon and bcsd is a table with a raster column "rast").
> for a set of rasters with increasing number of bands, and the time to run the
> query is shown in the attached plot. Since the raster properties are
> presumably shared across all the bands, it seems odd to me that run time
> would increase. I would expect it to be _contant_ (with constant number of
> pixels), but I suppose that that's my own ignorance as to how the PG type
> extensions work?
>
> Comments or explanations are welcome.
>
> ~James
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Is this a perverse use-case for raster types?
Hi All,
I'm considering using PostGIS rasters for storage of raster data at my
organization and I'm looking for some advice (or perhaps a reality check). I
work for a region climate services provider and the vast majority of our data
(by volume, not necessarily complexity) are output from climate models. These
are generally a n-by-m raster with one band for each timestep. There could be
upwards of 36k to 72k timesteps for a typical model run. We have hundreds of
model runs.
So my question is, is it insane to be thinking of storing that many bands in a
PostGIS raster? Or more specifically, is this _not_ a use case for which
PostGIS rasters were designed? I notice that most of the examples in the docs
and in "PostGIS In Action" focus only on images and I can imagine that handling
multispectral satellite images as being more of the intended use case.
I did a little benchmarking of a typical use case of ours ("What's the average
temperature inside a some polygon, e.g. a river basin?"). I noticed that the
run time for doing a ST_Clip(raster, band, geometry) and ST_Intersects(raster,
band, geometry) appears to be super-linear even when doing it on just a single
band. I ran the following query:
SELECT rid, st_height(st_clip(rast, 1, the_geom)), st_width(st_clip(rast,
the_geom)) FROM basins INNER JOIN bcsd ON ST_Intersects(rast, 1, the_geom)
WHERE rid = (where basins is table of river basins with one single
polygon and bcsd is a table with a raster column "rast").
for a set of rasters with increasing number of bands, and the time to run the
query is shown in the attached plot. Since the raster properties are
presumably shared across all the bands, it seems odd to me that run time would
increase. I would expect it to be _contant_ (with constant number of pixels),
but I suppose that that's my own ignorance as to how the PG type extensions
work?
Comments or explanations are welcome.
~James
--
James Hiebert
Lead, Computational Support
Pacific Climate Impacts Consortium
http://www.pacificclimate.org
Room 112, University House 1, University of Victoria
PO Box 1700 Sta CSC, Victoria, BC V8V 2Y2
E-mail: hieb...@uvic.ca
Tel: (250) 472-4521
Fax: (250) 472-4830
<>___________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2
No, it was not supported, it is in svn now, and will be at next patch release http://trac.osgeo.org/postgis/ticket/2071 P. On Mon, Oct 29, 2012 at 7:04 AM, Ilie, Radu wrote: > Yes, I did. The error is this: > > > > gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith > -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute > -Wformat-security -fno-strict-aliasing -fwrapv -g -fpic > -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include > -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include > -I/usr/include/libxml2 -I../liblwgeom -I. -I. > -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/server > -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/internal > -D_GNU_SOURCE -I/usr/include/libxml2 -c -o lwgeom_estimate.o > lwgeom_estimate.c > > lwgeom_estimate.c: In function ‘LWGEOM_gist_joinsel’: > > lwgeom_estimate.c:318: error: ‘Form_pg_class’ undeclared (first use in this > function) > > lwgeom_estimate.c:318: error: (Each undeclared identifier is reported only > once > > lwgeom_estimate.c:318: error: for each function it appears in.) > > lwgeom_estimate.c:318: error: expected ‘;’ before ‘reltup’ > > lwgeom_estimate.c:319: error: ‘reltup’ undeclared (first use in this > function) > > lwgeom_estimate.c:330: error: expected ‘;’ before ‘reltup’ > > make[1]: *** [lwgeom_estimate.o] Error 1 > > > > Radu > > > > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Jeff > Lake > Sent: Monday, October 29, 2012 9:59 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2 > > > > Did you specify the path to pg_config ?? > eg: > ./configure --with-pgconfig=/usr/pgsql-9.2/bin/pg_config > > Jeff Lake > MichiganWxSystem > AllisonHouse > GRLevelXStuff > > On 10/29/2012 9:49, Ilie, Radu wrote: > > Hi, > > > > I am getting build errors when trying to build PostGIS 1.5.5 against > PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it will > be fixed? > > > > Thank you, > > > > Radu Ilie > > > > > > > ___________ > > 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] out of memory
Yes it can... someday. -bborie On 10/26/2012 06:55 AM, Pierre Racine wrote: > This could be solved by ticket #826 > > http://trac.osgeo.org/postgis/ticket/826 > > Pierre > >> -Original Message- >> From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- >> boun...@postgis.refractions.net] On Behalf Of Bborie Park >> Sent: Friday, October 26, 2012 8:26 AM >> To: PostGIS Users Discussion >> Subject: Re: [postgis-users] out of memory >> >> The output size makes sense since the loader split the input raster >> into 100x100 tiles. >> >> 1591 / 100 = 15.91 ... 16 x 100 >> >> 1446 / 100 = 14.46 ... 15 x 100 >> >> So, when unioning the tiles back together, the unioned raster should >> be 1600 x 1500 (don't know where you got 1450 though). >> >> -bborie >> >> On Thu, Oct 25, 2012 at 10:43 PM, Mahavir Trivedi >> wrote: >>> hi i split the image (tiff) into 100 X 100 tile .(RASTER IMAGE 500 MB >>> input ) >>> but problem occurred when i export it then output size increase. >>> >>> (image input size = 1591 X 1446) >>> (image output size = 1600 X 1450) >>> >>> >>> can i change blocksize of server ? if yes then how ? >>> my system XP Windows 64-bit 4 GB RAM >>> >>> thanks >>> mahavir >>> >>> ___ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >> >> >> >> -- >> Bborie Park >> Programmer >> Center for Vectorborne Diseases >> UC Davis >> 530-752-8380 >> bkp...@ucdavis.edu >> ___ >> 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 > -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] out of memory
The image output looks correct to me. -bborie On 10/25/2012 10:43 PM, Mahavir Trivedi wrote: > hi i split the image (tiff) into 100 X 100 tile .(RASTER IMAGE 500 MB > input ) > but problem occurred when i export it then output size increase. > > (image input size = 1591 X 1446) > (image output size = 1600 X 1450) > > > can i change blocksize of server ? if yes then how ? > my system XP Windows 64-bit 4 GB RAM > > thanks > mahavir > > > > ___________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
uld give you a model to work with. > >>> >> >> >> > >>> >> >> >> -Steve W > >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > >>> >> >> >> On 10/29/2012 6:57 AM, Ed Linde wrote: > >>> >> >> >>> > >>> >> >> >>> Attached is a figure. Where the dotted line is the boundary > of > >>> >> >> >>> the > >>> >> >> >>> voronoi cell whose > >>> >> >> >>> generator is point P1. So triangle "4" intersects with the > >>> >> >> >>> voronoi > >>> >> >> >>> boundary, but we are > >>> >> >> >>> interested in the adjacent triangles of triangle 4, which are > >>> >> >> >>> closer > >>> >> >> >>> to > >>> >> >> >>> point P1. > >>> >> >> >>> For example, triangle 5. > >>> >> >> >>> > >>> >> >> >>> Hope this helps. > >>> >> >> >>> Cheers, > >>> >> >> >>> Ed > >>> >> >> >>> > >>> >> >> >>> On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot > >>> >> >> >>> >>> >> >> >>> <mailto:nicolas.ri...@gmail.com>> wrote: > >>> >> >> >>> > >>> >> >> >>> Could you draw a figure ? > >>> >> >> >>> > >>> >> >> >>> Nicolas > >>> >> >> >>> > >>> >> >> >>> On 29 October 2012 11:03, Ed Linde >>> >> >> >>> <mailto:edoli...@gmail.com>> wrote: > >>> >> >> >>> > Hi All, > >>> >> >> >>> > Thanks for the suggestions. > >>> >> >> >>> > For 1) I will look into how ST_touches works and see > if > >>> >> >> >>> it > >>> >> >> >>> can > >>> >> >> >>> pick up all > >>> >> >> >>> > the adjacent polygons to > >>> >> >> >>> > the one I have. And also look into Mike's suggestion > on > >>> >> >> >>> ST_relate...though I > >>> >> >> >>> > must admit it looks > >>> >> >> >>> > more complex. > >>> >> >> >>> > For 2) I will try to clarify it a bit more... its > harder > >>> >> >> >>> to > >>> >> >> >>> do > >>> >> >> >>> without a > >>> >> >> >>> > figure :) but here goes. > >>> >> >> >>> > > >>> >> >> >>> > Lets say we have a point Q which is the generator of a > >>> >> >> >>> voronoi > >>> >> >> >>> cell. Now I > >>> >> >> >>> > compute the > >>> >> >> >>> > intersection between the voronoi cell boundaries and > my > >>> >> >> >>> triangulation (Set > >>> >> >> >>> > of polygons) > >>> >> >> >>> > using ST_intersect. Once I have these triangles.. I > say > >>> >> >> >>> pick > >>> >> >> >>> one > >>> >> >> >>> triangle T > >>> >> >> >>> > that is > >>> >> >> >>> > intersecting the voronoi cell boundary of Q. > >>> >> >> >>> > For all the triangles adjacent to T, I need to know > >>> >> >> >>> which > >>> >> >> >>> triangles are > >>> >> >> >>> > INSIDE the voronoi > >>> >> >> >>> > boundary (closer to Q) and which adjacent triangles > are > >>&g
Re: [postgis-users] Difficult Problem with Polygons
>>> >> >> >>> >>> >> >> >>> On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot >>> >> >> >>> >> >> >> >>> <mailto:nicolas.ri...@gmail.com>> wrote: >>> >> >> >>> >>> >> >> >>> Could you draw a figure ? >>> >> >> >>> >>> >> >> >>> Nicolas >>> >> >> >>> >>> >> >> >>> On 29 October 2012 11:03, Ed Linde >> >> >> >>> <mailto:edoli...@gmail.com>> wrote: >>> >> >> >>> > Hi All, >>> >> >> >>> > Thanks for the suggestions. >>> >> >> >>> > For 1) I will look into how ST_touches works and see if >>> >> >> >>> it >>> >> >> >>> can >>> >> >> >>> pick up all >>> >> >> >>> > the adjacent polygons to >>> >> >> >>> > the one I have. And also look into Mike's suggestion on >>> >> >> >>> ST_relate...though I >>> >> >> >>> > must admit it looks >>> >> >> >>> > more complex. >>> >> >> >>> > For 2) I will try to clarify it a bit more... its harder >>> >> >> >>> to >>> >> >> >>> do >>> >> >> >>> without a >>> >> >> >>> > figure :) but here goes. >>> >> >> >>> > >>> >> >> >>> > Lets say we have a point Q which is the generator of a >>> >> >> >>> voronoi >>> >> >> >>> cell. Now I >>> >> >> >>> > compute the >>> >> >> >>> > intersection between the voronoi cell boundaries and my >>> >> >> >>> triangulation (Set >>> >> >> >>> > of polygons) >>> >> >> >>> > using ST_intersect. Once I have these triangles.. I say >>> >> >> >>> pick >>> >> >> >>> one >>> >> >> >>> triangle T >>> >> >> >>> > that is >>> >> >> >>> > intersecting the voronoi cell boundary of Q. >>> >> >> >>> > For all the triangles adjacent to T, I need to know >>> >> >> >>> which >>> >> >> >>> triangles are >>> >> >> >>> > INSIDE the voronoi >>> >> >> >>> > boundary (closer to Q) and which adjacent triangles are >>> >> >> >>> just >>> >> >> >>> OUTSIDE the >>> >> >> >>> > voronoi >>> >> >> >>> > boundary (farther from Q). I am basically testing for a >>> >> >> >>> certain >>> >> >> >>> property by >>> >> >> >>> > "shrinking" the >>> >> >> >>> > voronoi cell (closer to Q) and another property when >>> >> >> >>> "expanding" >>> >> >> >>> the voronoi >>> >> >> >>> > cell (away from Q). >>> >> >> >>> > Just need to make this division of triangles. Haven't >>> >> >> >>> thought >>> >> >> >>> of >>> >> >> >>> a nice way >>> >> >> >>> > to do this in postgis 2.0 >>> >> >> >>> > So any suggestions would greatly help. >>> >> >> >>> > >>> >> >> >>> > Thanks, >>> >> >> >>> > Ed >>> >> >> >>> > >>> >> >> >>> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews >>> >> >> >>> >> >> >> >>> <mailto:mwto...@gmail.com>> wrote: >>> >> >> >>> >> >>> >> >> >>> >> On 29 October 2012 21:33, Ed Linde >> >> >> >>> <mailto:edoli...@gmail.com>> wrote: >>> >> >> >>> >> > Hi All, >>> >> >> >>> >> > I need help with 2 hard problems. I store triangles >>> >> >> >>> in a >>> >> >> >>> table >>> >> >> >>> as >>> >> >> >>> >> > POLYGON. >>> >> >> >>> >> > >>> >> >> >>> >> > 1. I want to know for a given triangle, which >>> >> >> >>> triangles >>> >> >> >>> share >>> >> >> >>> an edge >>> >> >> >>> >> > (adjacent) with this triangle. >>> >> >> >>> >> >>> >> >> >>> >> Sounds like you have a finite element mesh with nodes >>> >> >> >>> and >>> >> >> >>> elements. >>> >> >> >>> >> You can use ST_Relate with pattern 'FF2F11212' to pick >>> >> >> >>> out >>> >> >> >>> elements >>> >> >> >>> >> that share the same edge. This DE-9-IM is sort-of a >>> >> >> >>> custom >>> >> >> >>> ST_Touches, >>> >> >> >>> >> but only takes linear boundary overlaps. So if you have >>> >> >> >>> a >>> >> >> >>> table >>> >> >> >>> >> "elements", and you want to find ones that touch ID >>> >> >> >>> 567: >>> >> >> >>> >> >>> >> >> >>> >> SELECT elements.* >>> >> >> >>> >> FROM elements, elements as e >>> >> >> >>> >> WHERE e.id <http://e.id> = 567 AND >>> >> >> >>> >>> >> >> >>> >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); >>> >> >> >>> >> >>> >> >> >>> >> I'm not certain about your second question. >>> >> >> >>> >> >>> >> >> >>> >> -Mike >>> >> >> >>> >> ___ >>> >> >> >>> >> postgis-users mailing list >>> >> >> >>> >> postgis-users@postgis.refractions.net >>> >> >> >>> <mailto:postgis-users@postgis.refractions.net> >>> >> >> >>> >>> >> >> >>> >> >>> >> >> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >> >> >>> > >>> >> >> >>> > >>> >> >> >>> > >>> >> >> >>> > ___ >>> >> >> >>> > postgis-users mailing list >>> >> >> >>> > postgis-users@postgis.refractions.net >>> >> >> >>> <mailto:postgis-users@postgis.refractions.net> >>> >> >> >>> >>> >> >> >>> > >>> >> >> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >> >> >>> > >>> >> >> >>> ___ >>> >> >> >>> postgis-users mailing list >>> >> >> >>> postgis-users@postgis.refractions.net >>> >> >> >>> <mailto: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 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 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 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 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
gt;> >> >> >> >> Hi Ed, >> >> >> >> >> >> >> >> Well if clarifies one thing at least, you can ignore Mike's >> >> >> >> st_Relate >> >> >> >> because 5 is not adjacent to a linear edge, it is only adjacent >> to a >> >> >> >> vertex, >> >> >> >> so st_touches should work fine. >> >> >> >> >> >> >> >> so you are looking for: >> >> >> >> >> >> >> >> 1. a specific triangle by id >> >> >> >> 2. that intersects triangle VC >> >> >> >> 3. and the triangles adjacent to triangle by id >> >> >> >> 4. and those that are contained in VC >> >> >> >> 5. sorted by distance to P1 limit 1 >> >> >> >> >> >> >> >> so something like: >> >> >> >> >> >> >> >> select id, the_geom >> >> >> >> from (select id, the_geom as adjacent >> >> >> >> from triangles >> >> >> >> where st_touches(the_geom, >> >> >> >> (select the_geom >> >> >> >> from triangles a, >> >> >> >> (select the_geom as vc >> >> >> >> from vc_table >> >> >> >> where id='p1') b >> >> >> >> where a.id=4)) c >> >> >> >> order by st_distance(c.the_geom, (select the_geom >> >> >> >> from points >> >> >> >> where id='p1')) asc limit 1; >> >> >> >> >> >> >> >> Untested, but should give you a model to work with. >> >> >> >> >> >> >> >> -Steve W >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> On 10/29/2012 6:57 AM, Ed Linde wrote: >> >> >> >>> >> >> >> >>> Attached is a figure. Where the dotted line is the boundary of >> the >> >> >> >>> voronoi cell whose >> >> >> >>> generator is point P1. So triangle "4" intersects with the >> voronoi >> >> >> >>> boundary, but we are >> >> >> >>> interested in the adjacent triangles of triangle 4, which are >> >> >> >>> closer >> >> >> >>> to >> >> >> >>> point P1. >> >> >> >>> For example, triangle 5. >> >> >> >>> >> >> >> >>> Hope this helps. >> >> >> >>> Cheers, >> >> >> >>> Ed >> >> >> >>> >> >> >> >>> On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot >> >> >> >>> > >> >> >>> <mailto:nicolas.ri...@gmail.com>> wrote: >> >> >> >>> >> >> >> >>> Could you draw a figure ? >> >> >> >>> >> >> >> >>> Nicolas >> >> >> >>> >> >> >> >>> On 29 October 2012 11:03, Ed Linde > >> >> >>> <mailto:edoli...@gmail.com>> wrote: >> >> >> >>> > Hi All, >> >> >> >>> > Thanks for the suggestions. >> >> >> >>> > For 1) I will look into how ST_touches works and see if >> it >> >> >> >>> can >> >> >> >>> pick up all >> >> >> >>> > the adjacent polygons to >> >> >> >>> > the one I have. And also look into Mike's suggestion on >> >> >> >>> ST_relate...though I >> >> >> >>> > must admit it looks >> >> >> >>> > more complex. >> >> >> >>> > For 2) I will try to clarify it a bit more... its harder >> to >> >> >> >>> do >> >&g
Re: [postgis-users] Difficult Problem with Polygons
t;> >> (select the_geom > >> >> >> from triangles a, > >> >> >> (select the_geom as vc > >> >> >> from vc_table > >> >> >> where id='p1') b > >> >> >> where a.id=4)) c > >> >> >> order by st_distance(c.the_geom, (select the_geom > >> >> >> from points > >> >> >> where id='p1')) asc limit 1; > >> >> >> > >> >> >> Untested, but should give you a model to work with. > >> >> >> > >> >> >> -Steve W > >> >> >> > >> >> >> > >> >> >> > >> >> >> On 10/29/2012 6:57 AM, Ed Linde wrote: > >> >> >>> > >> >> >>> Attached is a figure. Where the dotted line is the boundary of > the > >> >> >>> voronoi cell whose > >> >> >>> generator is point P1. So triangle "4" intersects with the > voronoi > >> >> >>> boundary, but we are > >> >> >>> interested in the adjacent triangles of triangle 4, which are > >> >> >>> closer > >> >> >>> to > >> >> >>> point P1. > >> >> >>> For example, triangle 5. > >> >> >>> > >> >> >>> Hope this helps. > >> >> >>> Cheers, > >> >> >>> Ed > >> >> >>> > >> >> >>> On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot > >> >> >>> >> >> >>> <mailto:nicolas.ri...@gmail.com>> wrote: > >> >> >>> > >> >> >>> Could you draw a figure ? > >> >> >>> > >> >> >>> Nicolas > >> >> >>> > >> >> >>> On 29 October 2012 11:03, Ed Linde >> >> >>> <mailto:edoli...@gmail.com>> wrote: > >> >> >>> > Hi All, > >> >> >>> > Thanks for the suggestions. > >> >> >>> > For 1) I will look into how ST_touches works and see if it > >> >> >>> can > >> >> >>> pick up all > >> >> >>> > the adjacent polygons to > >> >> >>> > the one I have. And also look into Mike's suggestion on > >> >> >>> ST_relate...though I > >> >> >>> > must admit it looks > >> >> >>> > more complex. > >> >> >>> > For 2) I will try to clarify it a bit more... its harder > to > >> >> >>> do > >> >> >>> without a > >> >> >>> > figure :) but here goes. > >> >> >>> > > >> >> >>> > Lets say we have a point Q which is the generator of a > >> >> >>> voronoi > >> >> >>> cell. Now I > >> >> >>> > compute the > >> >> >>> > intersection between the voronoi cell boundaries and my > >> >> >>> triangulation (Set > >> >> >>> > of polygons) > >> >> >>> > using ST_intersect. Once I have these triangles.. I say > pick > >> >> >>> one > >> >> >>> triangle T > >> >> >>> > that is > >> >> >>> > intersecting the voronoi cell boundary of Q. > >> >> >>> > For all the triangles adjacent to T, I need to know which > >> >> >>> triangles are > >> >> >>> > INSIDE the voronoi > >> >> >>> > boundary (closer to Q) and which adjacent triangles are > just > >> >> >>> OUTSIDE the > >> >> >>> > voronoi > >> >> >>> > boundary (farther from Q). I am basically testing for a > >> >> >>> certain > >
Re: [postgis-users] Difficult Problem with Polygons
ve you a model to work with. >> >> >> >> >> >> -Steve W >> >> >> >> >> >> >> >> >> >> >> >> On 10/29/2012 6:57 AM, Ed Linde wrote: >> >> >>> >> >> >>> Attached is a figure. Where the dotted line is the boundary of the >> >> >>> voronoi cell whose >> >> >>> generator is point P1. So triangle "4" intersects with the voronoi >> >> >>> boundary, but we are >> >> >>> interested in the adjacent triangles of triangle 4, which are >> >> >>> closer >> >> >>> to >> >> >>> point P1. >> >> >>> For example, triangle 5. >> >> >>> >> >> >>> Hope this helps. >> >> >>> Cheers, >> >> >>> Ed >> >> >>> >> >> >>> On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot >> >> >>> > >> >>> <mailto:nicolas.ri...@gmail.com>> wrote: >> >> >>> >> >> >>> Could you draw a figure ? >> >> >>> >> >> >>> Nicolas >> >> >>> >> >> >>> On 29 October 2012 11:03, Ed Linde > >> >>> <mailto:edoli...@gmail.com>> wrote: >> >> >>> > Hi All, >> >> >>> > Thanks for the suggestions. >> >> >>> > For 1) I will look into how ST_touches works and see if it >> >> >>> can >> >> >>> pick up all >> >> >>> > the adjacent polygons to >> >> >>> > the one I have. And also look into Mike's suggestion on >> >> >>> ST_relate...though I >> >> >>> > must admit it looks >> >> >>> > more complex. >> >> >>> > For 2) I will try to clarify it a bit more... its harder to >> >> >>> do >> >> >>> without a >> >> >>> > figure :) but here goes. >> >> >>> > >> >> >>> > Lets say we have a point Q which is the generator of a >> >> >>> voronoi >> >> >>> cell. Now I >> >> >>> > compute the >> >> >>> > intersection between the voronoi cell boundaries and my >> >> >>> triangulation (Set >> >> >>> > of polygons) >> >> >>> > using ST_intersect. Once I have these triangles.. I say pick >> >> >>> one >> >> >>> triangle T >> >> >>> > that is >> >> >>> > intersecting the voronoi cell boundary of Q. >> >> >>> > For all the triangles adjacent to T, I need to know which >> >> >>> triangles are >> >> >>> > INSIDE the voronoi >> >> >>> > boundary (closer to Q) and which adjacent triangles are just >> >> >>> OUTSIDE the >> >> >>> > voronoi >> >> >>> > boundary (farther from Q). I am basically testing for a >> >> >>> certain >> >> >>> property by >> >> >>> > "shrinking" the >> >> >>> > voronoi cell (closer to Q) and another property when >> >> >>> "expanding" >> >> >>> the voronoi >> >> >>> > cell (away from Q). >> >> >>> > Just need to make this division of triangles. Haven't >> >> >>> thought >> >> >>> of >> >> >>> a nice way >> >> >>> > to do this in postgis 2.0 >> >> >>> > So any suggestions would greatly help. >> >> >>> > >> >> >>> > Thanks, >> >> >>> > Ed >> >> >>> > >> >> >>> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews >> >> >>> > >> >>> <mailto:mwto...@gmail.com>> wrote: >> >> >>> >> >> >> >>> >> On 29 October 2012 21:33, Ed Lind
Re: [postgis-users] Difficult Problem with Polygons
pe this helps. > >> >>> Cheers, > >> >>> Ed > >> >>> > >> >>> On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot > >> >>> >> >>> <mailto:nicolas.ri...@gmail.com>> wrote: > >> >>> > >> >>> Could you draw a figure ? > >> >>> > >> >>> Nicolas > >> >>> > >> >>> On 29 October 2012 11:03, Ed Linde >> >>> <mailto:edoli...@gmail.com>> wrote: > >> >>> > Hi All, > >> >>> > Thanks for the suggestions. > >> >>> > For 1) I will look into how ST_touches works and see if it > can > >> >>> pick up all > >> >>> > the adjacent polygons to > >> >>> > the one I have. And also look into Mike's suggestion on > >> >>> ST_relate...though I > >> >>> > must admit it looks > >> >>> > more complex. > >> >>> > For 2) I will try to clarify it a bit more... its harder to > do > >> >>> without a > >> >>> > figure :) but here goes. > >> >>> > > >> >>> > Lets say we have a point Q which is the generator of a > voronoi > >> >>> cell. Now I > >> >>> > compute the > >> >>> > intersection between the voronoi cell boundaries and my > >> >>> triangulation (Set > >> >>> > of polygons) > >> >>> > using ST_intersect. Once I have these triangles.. I say pick > >> >>> one > >> >>> triangle T > >> >>> > that is > >> >>> > intersecting the voronoi cell boundary of Q. > >> >>> > For all the triangles adjacent to T, I need to know which > >> >>> triangles are > >> >>> > INSIDE the voronoi > >> >>> > boundary (closer to Q) and which adjacent triangles are just > >> >>> OUTSIDE the > >> >>> > voronoi > >> >>> > boundary (farther from Q). I am basically testing for a > certain > >> >>> property by > >> >>> > "shrinking" the > >> >>> > voronoi cell (closer to Q) and another property when > >> >>> "expanding" > >> >>> the voronoi > >> >>> > cell (away from Q). > >> >>> > Just need to make this division of triangles. Haven't thought > >> >>> of > >> >>> a nice way > >> >>> > to do this in postgis 2.0 > >> >>> > So any suggestions would greatly help. > >> >>> > > >> >>> > Thanks, > >> >>> > Ed > >> >>> > > >> >>> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews < > mwto...@gmail.com > >> >>> <mailto:mwto...@gmail.com>> wrote: > >> >>> >> > >> >>> >> On 29 October 2012 21:33, Ed Linde >> >>> <mailto:edoli...@gmail.com>> wrote: > >> >>> >> > Hi All, > >> >>> >> > I need help with 2 hard problems. I store triangles in a > >> >>> table > >> >>> as > >> >>> >> > POLYGON. > >> >>> >> > > >> >>> >> > 1. I want to know for a given triangle, which triangles > >> >>> share > >> >>> an edge > >> >>> >> > (adjacent) with this triangle. > >> >>> >> > >> >>> >> Sounds like you have a finite element mesh with nodes and > >> >>> elements. > >> >>> >> You can use ST_Relate with pattern 'FF2F11212' to pick out > >> >>> elements > >> >>> >> that share the same edge. This DE-9-IM is sort-of a custom > >> >>> ST_Touches, > >> >>> >> but only takes linear boundary overlaps. So if you have a > >> >>> table > >> >>> >> "elements", and you want to find ones that touch ID 567: > >> >>> >> > >> >>> >> SELECT elements.* > >> >>> >> FROM elements, elements as e > >> >>> >> WHERE e.id <http://e.id> = 567 AND > >> >>> > >> >>> >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); > >> >>> >> > >> >>> >> I'm not certain about your second question. > >> >>> >> > >> >>> >> -Mike > >> >>> >> ___ > >> >>> >> postgis-users mailing list > >> >>> >> postgis-users@postgis.refractions.net > >> >>> <mailto:postgis-users@postgis.refractions.net> > >> >>> > >> >>> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users > >> >>> > > >> >>> > > >> >>> > > >> >>> > ___ > >> >>> > postgis-users mailing list > >> >>> > postgis-users@postgis.refractions.net > >> >>> <mailto:postgis-users@postgis.refractions.net> > >> >>> > >> >>> > > http://postgis.refractions.net/mailman/listinfo/postgis-users > >> >>> > > >> >>> ___ > >> >>> postgis-users mailing list > >> >>> postgis-users@postgis.refractions.net > >> >>> <mailto: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 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 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 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] Difficult Problem with Polygons
and see if it can >> >>> pick up all >> >>> > the adjacent polygons to >> >>> > the one I have. And also look into Mike's suggestion on >> >>> ST_relate...though I >> >>> > must admit it looks >> >>> > more complex. >> >>> > For 2) I will try to clarify it a bit more... its harder to do >> >>> without a >> >>> > figure :) but here goes. >> >>> > >> >>> > Lets say we have a point Q which is the generator of a voronoi >> >>> cell. Now I >> >>> > compute the >> >>> > intersection between the voronoi cell boundaries and my >> >>> triangulation (Set >> >>> > of polygons) >> >>> > using ST_intersect. Once I have these triangles.. I say pick >> >>> one >> >>> triangle T >> >>> > that is >> >>> > intersecting the voronoi cell boundary of Q. >> >>> > For all the triangles adjacent to T, I need to know which >> >>> triangles are >> >>> > INSIDE the voronoi >> >>> > boundary (closer to Q) and which adjacent triangles are just >> >>> OUTSIDE the >> >>> > voronoi >> >>> > boundary (farther from Q). I am basically testing for a certain >> >>> property by >> >>> > "shrinking" the >> >>> > voronoi cell (closer to Q) and another property when >> >>> "expanding" >> >>> the voronoi >> >>> > cell (away from Q). >> >>> > Just need to make this division of triangles. Haven't thought >> >>> of >> >>> a nice way >> >>> > to do this in postgis 2.0 >> >>> > So any suggestions would greatly help. >> >>> > >> >>> > Thanks, >> >>> > Ed >> >>> > >> >>> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews > >>> <mailto:mwto...@gmail.com>> wrote: >> >>> >> >> >>> >> On 29 October 2012 21:33, Ed Linde > >>> <mailto:edoli...@gmail.com>> wrote: >> >>> >> > Hi All, >> >>> >> > I need help with 2 hard problems. I store triangles in a >> >>> table >> >>> as >> >>> >> > POLYGON. >> >>> >> > >> >>> >> > 1. I want to know for a given triangle, which triangles >> >>> share >> >>> an edge >> >>> >> > (adjacent) with this triangle. >> >>> >> >> >>> >> Sounds like you have a finite element mesh with nodes and >> >>> elements. >> >>> >> You can use ST_Relate with pattern 'FF2F11212' to pick out >> >>> elements >> >>> >> that share the same edge. This DE-9-IM is sort-of a custom >> >>> ST_Touches, >> >>> >> but only takes linear boundary overlaps. So if you have a >> >>> table >> >>> >> "elements", and you want to find ones that touch ID 567: >> >>> >> >> >>> >> SELECT elements.* >> >>> >> FROM elements, elements as e >> >>> >> WHERE e.id <http://e.id> = 567 AND >> >>> >> >>> >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); >> >>> >> >> >>> >> I'm not certain about your second question. >> >>> >> >> >>> >> -Mike >> >>> >> ___ >> >>> >> postgis-users mailing list >> >>> >> postgis-users@postgis.refractions.net >> >>> <mailto:postgis-users@postgis.refractions.net> >> >>> >> >>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >>> > >> >>> > >> >>> > >> >>> > ___ >> >>> > postgis-users mailing list >> >>> > postgis-users@postgis.refractions.net >> >>> <mailto:postgis-users@postgis.refractions.net> >> >>> >> >>> > http://postgis.refractions.net/mailman/listinfo/postgis-users >> >>> > >> >>> ___ >> >>> postgis-users mailing list >> >>> postgis-users@postgis.refractions.net >> >>> <mailto: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 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 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
> >>> > that is > >>> > intersecting the voronoi cell boundary of Q. > >>> > For all the triangles adjacent to T, I need to know which > >>> triangles are > >>> > INSIDE the voronoi > >>> > boundary (closer to Q) and which adjacent triangles are just > >>> OUTSIDE the > >>> > voronoi > >>> > boundary (farther from Q). I am basically testing for a certain > >>> property by > >>> > "shrinking" the > >>> > voronoi cell (closer to Q) and another property when "expanding" > >>> the voronoi > >>> > cell (away from Q). > >>> > Just need to make this division of triangles. Haven't thought of > >>> a nice way > >>> > to do this in postgis 2.0 > >>> > So any suggestions would greatly help. > >>> > > >>> > Thanks, > >>> > Ed > >>> > > >>> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews >>> <mailto:mwto...@gmail.com>> wrote: > >>> >> > >>> >> On 29 October 2012 21:33, Ed Linde >>> <mailto:edoli...@gmail.com>> wrote: > >>> >> > Hi All, > >>> >> > I need help with 2 hard problems. I store triangles in a > table > >>> as > >>> >> > POLYGON. > >>> >> > > >>> >> > 1. I want to know for a given triangle, which triangles share > >>> an edge > >>> >> > (adjacent) with this triangle. > >>> >> > >>> >> Sounds like you have a finite element mesh with nodes and > >>> elements. > >>> >> You can use ST_Relate with pattern 'FF2F11212' to pick out > >>> elements > >>> >> that share the same edge. This DE-9-IM is sort-of a custom > >>> ST_Touches, > >>> >> but only takes linear boundary overlaps. So if you have a table > >>> >> "elements", and you want to find ones that touch ID 567: > >>> >> > >>> >> SELECT elements.* > >>> >> FROM elements, elements as e > >>> >> WHERE e.id <http://e.id> = 567 AND > >>> > >>> >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); > >>> >> > >>> >> I'm not certain about your second question. > >>> >> > >>> >> -Mike > >>> >> ___ > >>> >> postgis-users mailing list > >>> >> postgis-users@postgis.refractions.net > >>> <mailto:postgis-users@postgis.refractions.net> > >>> > >>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users > >>> > > >>> > > >>> > > >>> > ___ > >>> > postgis-users mailing list > >>> > postgis-users@postgis.refractions.net > >>> <mailto:postgis-users@postgis.refractions.net> > >>> > >>> > http://postgis.refractions.net/mailman/listinfo/postgis-users > >>> > > >>> ___ > >>> postgis-users mailing list > >>> postgis-users@postgis.refractions.net > >>> <mailto: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 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 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] Difficult Problem with Polygons
;>> > >>> > Thanks, >>> > Ed >>> > >>> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews >> <mailto:mwto...@gmail.com>> wrote: >>> >> >>> >> On 29 October 2012 21:33, Ed Linde >> <mailto:edoli...@gmail.com>> wrote: >>> >> > Hi All, >>> >> > I need help with 2 hard problems. I store triangles in a table >>> as >>> >> > POLYGON. >>> >> > >>> >> > 1. I want to know for a given triangle, which triangles share >>> an edge >>> >> > (adjacent) with this triangle. >>> >> >>> >> Sounds like you have a finite element mesh with nodes and >>> elements. >>> >> You can use ST_Relate with pattern 'FF2F11212' to pick out >>> elements >>> >> that share the same edge. This DE-9-IM is sort-of a custom >>> ST_Touches, >>> >> but only takes linear boundary overlaps. So if you have a table >>> >> "elements", and you want to find ones that touch ID 567: >>> >> >>> >> SELECT elements.* >>> >> FROM elements, elements as e >>> >> WHERE e.id <http://e.id> = 567 AND >>> >>> >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); >>> >> >>> >> I'm not certain about your second question. >>> >> >>> >> -Mike >>> >> ___ >>> >> postgis-users mailing list >>> >> postgis-users@postgis.refractions.net >>> <mailto:postgis-users@postgis.refractions.net> >>> >>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> > >>> > >>> > >>> > ___ >>> > postgis-users mailing list >>> > postgis-users@postgis.refractions.net >>> <mailto:postgis-users@postgis.refractions.net> >>> >>> > http://postgis.refractions.net/mailman/listinfo/postgis-users >>> > >>> ___ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> <mailto: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 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
wrote: >>> > Hi All, >>> > Thanks for the suggestions. >>> > For 1) I will look into how ST_touches works and see if it can >>> pick up all >>> > the adjacent polygons to >>> > the one I have. And also look into Mike's suggestion on >>> ST_relate...though I >>> > must admit it looks >>> > more complex. >>> > For 2) I will try to clarify it a bit more... its harder to do >>> without a >>> > figure :) but here goes. >>> > >>> > Lets say we have a point Q which is the generator of a voronoi >>> cell. Now I >>> > compute the >>> > intersection between the voronoi cell boundaries and my >>> triangulation (Set >>> > of polygons) >>> > using ST_intersect. Once I have these triangles.. I say pick one >>> triangle T >>> > that is >>> > intersecting the voronoi cell boundary of Q. >>> > For all the triangles adjacent to T, I need to know which >>> triangles are >>> > INSIDE the voronoi >>> > boundary (closer to Q) and which adjacent triangles are just >>> OUTSIDE the >>> > voronoi >>> > boundary (farther from Q). I am basically testing for a certain >>> property by >>> > "shrinking" the >>> > voronoi cell (closer to Q) and another property when "expanding" >>> the voronoi >>> > cell (away from Q). >>> > Just need to make this division of triangles. Haven't thought of >>> a nice way >>> > to do this in postgis 2.0 >>> > So any suggestions would greatly help. >>> > >>> > Thanks, >>> > Ed >>> > >>> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews >> <mailto:mwto...@gmail.com>> wrote: >>> >> >>> >> On 29 October 2012 21:33, Ed Linde >> <mailto:edoli...@gmail.com>> wrote: >>> >> > Hi All, >>> >> > I need help with 2 hard problems. I store triangles in a table >>> as >>> >> > POLYGON. >>> >> > >>> >> > 1. I want to know for a given triangle, which triangles share >>> an edge >>> >> > (adjacent) with this triangle. >>> >> >>> >> Sounds like you have a finite element mesh with nodes and >>> elements. >>> >> You can use ST_Relate with pattern 'FF2F11212' to pick out >>> elements >>> >> that share the same edge. This DE-9-IM is sort-of a custom >>> ST_Touches, >>> >> but only takes linear boundary overlaps. So if you have a table >>> >> "elements", and you want to find ones that touch ID 567: >>> >> >>> >> SELECT elements.* >>> >> FROM elements, elements as e >>> >> WHERE e.id <http://e.id> = 567 AND >>> >>> >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); >>> >> >>> >> I'm not certain about your second question. >>> >> >>> >> -Mike >>> >> __________**_ >>> >> postgis-users mailing list >>> >> >>> postgis-users@postgis.**refractions.net >>> >>> <mailto:postgis-users@postgis.**refractions.net >>> > >>> >>> >> http://postgis.refractions.**net/mailman/listinfo/postgis-** >>> users <http://postgis.refractions.net/mailman/listinfo/postgis-users> >>> > >>> > >>> > >>> > __**_ >>> > postgis-users mailing list >>> > >>> postgis-users@postgis.**refractions.net >>> >>> <mailto:postgis-users@postgis.**refractions.net >>> > >>> >>> > >>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users> >>> > >>> __**_ >>> postgis-users mailing list >>> >>> postgis-users@postgis.**refractions.net >>> >>> <mailto:postgis-users@postgis.**refractions.net >>> > >>> >>> >>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<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<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<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] Difficult Problem with Polygons
Hi All, Thanks for the tips! Just another thing, when I compute the "transition points" on each edge (shown as red points in my pdf). I need to join them to make a polygon. Wondering how I can connect them together so that I start with a point and end on it to form a closed polygon? Cheers, Ed On Mon, Oct 29, 2012 at 3:03 PM, Stephen Woodbridge wrote: > Hi Ed, > > Well if clarifies one thing at least, you can ignore Mike's st_Relate > because 5 is not adjacent to a linear edge, it is only adjacent to a > vertex, so st_touches should work fine. > > so you are looking for: > > 1. a specific triangle by id > 2. that intersects triangle VC > 3. and the triangles adjacent to triangle by id > 4. and those that are contained in VC > 5. sorted by distance to P1 limit 1 > > so something like: > > select id, the_geom > from (select id, the_geom as adjacent > from triangles > where st_touches(the_geom, > (select the_geom > from triangles a, > (select the_geom as vc > from vc_table > where id='p1') b > where a.id=4)) c > order by st_distance(c.the_geom, (select the_geom > from points > where id='p1')) asc limit 1; > > Untested, but should give you a model to work with. > > -Steve W > > > > On 10/29/2012 6:57 AM, Ed Linde wrote: > >> Attached is a figure. Where the dotted line is the boundary of the >> voronoi cell whose >> generator is point P1. So triangle "4" intersects with the voronoi >> boundary, but we are >> interested in the adjacent triangles of triangle 4, which are closer to >> point P1. >> For example, triangle 5. >> >> Hope this helps. >> Cheers, >> Ed >> >> On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot > <mailto:nicolas.ribot@gmail.**com >> wrote: >> >> Could you draw a figure ? >> >> Nicolas >> >> On 29 October 2012 11:03, Ed Linde > <mailto:edoli...@gmail.com>> wrote: >> > Hi All, >> > Thanks for the suggestions. >> > For 1) I will look into how ST_touches works and see if it can >> pick up all >> > the adjacent polygons to >> > the one I have. And also look into Mike's suggestion on >> ST_relate...though I >> > must admit it looks >> > more complex. >> > For 2) I will try to clarify it a bit more... its harder to do >> without a >> > figure :) but here goes. >> > >> > Lets say we have a point Q which is the generator of a voronoi >> cell. Now I >> > compute the >> > intersection between the voronoi cell boundaries and my >> triangulation (Set >> > of polygons) >> > using ST_intersect. Once I have these triangles.. I say pick one >> triangle T >> > that is >> > intersecting the voronoi cell boundary of Q. >> > For all the triangles adjacent to T, I need to know which >> triangles are >> > INSIDE the voronoi >> > boundary (closer to Q) and which adjacent triangles are just >> OUTSIDE the >> > voronoi >> > boundary (farther from Q). I am basically testing for a certain >> property by >> > "shrinking" the >> > voronoi cell (closer to Q) and another property when "expanding" >> the voronoi >> > cell (away from Q). >> > Just need to make this division of triangles. Haven't thought of >> a nice way >> > to do this in postgis 2.0 >> > So any suggestions would greatly help. >> > >> > Thanks, >> > Ed >> > >> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews > <mailto:mwto...@gmail.com>> wrote: >> >> >> >> On 29 October 2012 21:33, Ed Linde > <mailto:edoli...@gmail.com>> wrote: >> >> > Hi All, >> >> > I need help with 2 hard problems. I store triangles in a table >> as >> >> > POLYGON. >> >> > >> >> > 1. I want to know for a given triangle, which triangles share >> an edge >> >> > (adjacent) with this triangle. >&
Re: [postgis-users] Difficult Problem with Polygons
Hi Ed,
Well if clarifies one thing at least, you can ignore Mike's st_Relate
because 5 is not adjacent to a linear edge, it is only adjacent to a
vertex, so st_touches should work fine.
so you are looking for:
1. a specific triangle by id
2. that intersects triangle VC
3. and the triangles adjacent to triangle by id
4. and those that are contained in VC
5. sorted by distance to P1 limit 1
so something like:
select id, the_geom
from (select id, the_geom as adjacent
from triangles
where st_touches(the_geom,
(select the_geom
from triangles a,
(select the_geom as vc
from vc_table
where id='p1') b
where a.id=4)) c
order by st_distance(c.the_geom, (select the_geom
from points
where id='p1')) asc limit 1;
Untested, but should give you a model to work with.
-Steve W
On 10/29/2012 6:57 AM, Ed Linde wrote:
Attached is a figure. Where the dotted line is the boundary of the
voronoi cell whose
generator is point P1. So triangle "4" intersects with the voronoi
boundary, but we are
interested in the adjacent triangles of triangle 4, which are closer to
point P1.
For example, triangle 5.
Hope this helps.
Cheers,
Ed
On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot mailto:nicolas.ri...@gmail.com>> wrote:
Could you draw a figure ?
Nicolas
On 29 October 2012 11:03, Ed Linde mailto:edoli...@gmail.com>> wrote:
> Hi All,
> Thanks for the suggestions.
> For 1) I will look into how ST_touches works and see if it can
pick up all
> the adjacent polygons to
> the one I have. And also look into Mike's suggestion on
ST_relate...though I
> must admit it looks
> more complex.
> For 2) I will try to clarify it a bit more... its harder to do
without a
> figure :) but here goes.
>
> Lets say we have a point Q which is the generator of a voronoi
cell. Now I
> compute the
> intersection between the voronoi cell boundaries and my
triangulation (Set
> of polygons)
> using ST_intersect. Once I have these triangles.. I say pick one
triangle T
> that is
> intersecting the voronoi cell boundary of Q.
> For all the triangles adjacent to T, I need to know which
triangles are
> INSIDE the voronoi
> boundary (closer to Q) and which adjacent triangles are just
OUTSIDE the
> voronoi
> boundary (farther from Q). I am basically testing for a certain
property by
> "shrinking" the
> voronoi cell (closer to Q) and another property when "expanding"
the voronoi
> cell (away from Q).
> Just need to make this division of triangles. Haven't thought of
a nice way
> to do this in postgis 2.0
> So any suggestions would greatly help.
>
> Thanks,
> Ed
>
> On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews mailto:mwto...@gmail.com>> wrote:
>>
>> On 29 October 2012 21:33, Ed Linde mailto:edoli...@gmail.com>> wrote:
>> > Hi All,
>> > I need help with 2 hard problems. I store triangles in a table as
>> > POLYGON.
>> >
>> > 1. I want to know for a given triangle, which triangles share
an edge
>> > (adjacent) with this triangle.
>>
>> Sounds like you have a finite element mesh with nodes and elements.
>> You can use ST_Relate with pattern 'FF2F11212' to pick out elements
>> that share the same edge. This DE-9-IM is sort-of a custom
ST_Touches,
>> but only takes linear boundary overlaps. So if you have a table
>> "elements", and you want to find ones that touch ID 567:
>>
>> SELECT elements.*
>> FROM elements, elements as e
>> WHERE e.id <http://e.id> = 567 AND
>> ST_Relate(elements.geom, e.geom, 'FF2F11212');
>>
>> I'm not certain about your second question.
>>
>> -Mike
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
<mailto:postgis-users@postgis.refractions.net>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
<mailto:postgis-users
Re: [postgis-users] Difficult Problem with Polygons
Yes indeed it's clearer. You could compute the distance between each adjacent triangle and the voronoi generator and order by shortest distance. (taking the triangle's centroid for the distance) Also, is the voronoi boundary forming a polygon or just a line ? in the former, you could find all the triangles inside the voronoi cell. In the latter, you will have to determine a signed area or a cross product to know if points lie in the same side of the boundary. Nicolas On 29 October 2012 13:45, Ed Linde wrote: > Hi All, > Wondering if that diagram made things any simpler or is it still not clear > what the problem > is? > > Thanks, > Ed > > > On Mon, Oct 29, 2012 at 11:57 AM, Ed Linde wrote: >> >> Attached is a figure. Where the dotted line is the boundary of the voronoi >> cell whose >> generator is point P1. So triangle "4" intersects with the voronoi >> boundary, but we are >> interested in the adjacent triangles of triangle 4, which are closer to >> point P1. >> For example, triangle 5. >> >> Hope this helps. >> Cheers, >> Ed >> >> >> On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot >> wrote: >>> >>> Could you draw a figure ? >>> >>> Nicolas >>> >>> On 29 October 2012 11:03, Ed Linde wrote: >>> > Hi All, >>> > Thanks for the suggestions. >>> > For 1) I will look into how ST_touches works and see if it can pick up >>> > all >>> > the adjacent polygons to >>> > the one I have. And also look into Mike's suggestion on >>> > ST_relate...though I >>> > must admit it looks >>> > more complex. >>> > For 2) I will try to clarify it a bit more... its harder to do without >>> > a >>> > figure :) but here goes. >>> > >>> > Lets say we have a point Q which is the generator of a voronoi cell. >>> > Now I >>> > compute the >>> > intersection between the voronoi cell boundaries and my triangulation >>> > (Set >>> > of polygons) >>> > using ST_intersect. Once I have these triangles.. I say pick one >>> > triangle T >>> > that is >>> > intersecting the voronoi cell boundary of Q. >>> > For all the triangles adjacent to T, I need to know which triangles are >>> > INSIDE the voronoi >>> > boundary (closer to Q) and which adjacent triangles are just OUTSIDE >>> > the >>> > voronoi >>> > boundary (farther from Q). I am basically testing for a certain >>> > property by >>> > "shrinking" the >>> > voronoi cell (closer to Q) and another property when "expanding" the >>> > voronoi >>> > cell (away from Q). >>> > Just need to make this division of triangles. Haven't thought of a nice >>> > way >>> > to do this in postgis 2.0 >>> > So any suggestions would greatly help. >>> > >>> > Thanks, >>> > Ed >>> > >>> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews wrote: >>> >> >>> >> On 29 October 2012 21:33, Ed Linde wrote: >>> >> > Hi All, >>> >> > I need help with 2 hard problems. I store triangles in a table as >>> >> > POLYGON. >>> >> > >>> >> > 1. I want to know for a given triangle, which triangles share an >>> >> > edge >>> >> > (adjacent) with this triangle. >>> >> >>> >> Sounds like you have a finite element mesh with nodes and elements. >>> >> You can use ST_Relate with pattern 'FF2F11212' to pick out elements >>> >> that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, >>> >> but only takes linear boundary overlaps. So if you have a table >>> >> "elements", and you want to find ones that touch ID 567: >>> >> >>> >> SELECT elements.* >>> >> FROM elements, elements as e >>> >> WHERE e.id = 567 AND >>> >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); >>> >> >>> >> I'm not certain about your second question. >>> >> >>> >> -Mike >>> >> ___ >>> >> 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 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2
Yes, I did. The error is this: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -fpic -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include -I/usr/include/libxml2 -I../liblwgeom -I. -I. -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/server -I/perforce/DATARC_auto/DATARC/products/AggServer/dev/main/BUILD_AREA/INSTALL/usr/local/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o lwgeom_estimate.o lwgeom_estimate.c lwgeom_estimate.c: In function 'LWGEOM_gist_joinsel': lwgeom_estimate.c:318: error: 'Form_pg_class' undeclared (first use in this function) lwgeom_estimate.c:318: error: (Each undeclared identifier is reported only once lwgeom_estimate.c:318: error: for each function it appears in.) lwgeom_estimate.c:318: error: expected ';' before 'reltup' lwgeom_estimate.c:319: error: 'reltup' undeclared (first use in this function) lwgeom_estimate.c:330: error: expected ';' before 'reltup' make[1]: *** [lwgeom_estimate.o] Error 1 Radu From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Jeff Lake Sent: Monday, October 29, 2012 9:59 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS 1.5.5 and PostgreSQL 9.2 Did you specify the path to pg_config ?? eg: ./configure --with-pgconfig=/usr/pgsql-9.2/bin/pg_config Jeff Lake MichiganWxSystem<http://www.michiganwxsystem.com> AllisonHouse<http://www.allisonhouse.com> GRLevelXStuff<http://www.grlevelxstuff.com> On 10/29/2012 9:49, Ilie, Radu wrote: Hi, I am getting build errors when trying to build PostGIS 1.5.5 against PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it will be fixed? Thank you, Radu Ilie ___ postgis-users mailing list postgis-users@postgis.refractions.net<mailto: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 1.5.5 and PostgreSQL 9.2
Did you specify the path to pg_config ?? eg: ./configure --with-pgconfig=/usr/pgsql-9.2/bin/pg_config *Jeff Lake* MichiganWxSystem <http://www.michiganwxsystem.com> AllisonHouse <http://www.allisonhouse.com> GRLevelXStuff <http://www.grlevelxstuff.com> On 10/29/2012 9:49, Ilie, Radu wrote: Hi, I am getting build errors when trying to build PostGIS 1.5.5 against PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it will be fixed? Thank you, Radu Ilie _______ 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] PostGIS 1.5.5 and PostgreSQL 9.2
Hi, I am getting build errors when trying to build PostGIS 1.5.5 against PostgreSQL 9.2.1. Is this a known problem? If yes, do you know when it will be fixed? Thank you, Radu Ilie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
Hi All, Wondering if that diagram made things any simpler or is it still not clear what the problem is? Thanks, Ed On Mon, Oct 29, 2012 at 11:57 AM, Ed Linde wrote: > Attached is a figure. Where the dotted line is the boundary of the voronoi > cell whose > generator is point P1. So triangle "4" intersects with the voronoi > boundary, but we are > interested in the adjacent triangles of triangle 4, which are closer to > point P1. > For example, triangle 5. > > Hope this helps. > Cheers, > Ed > > > On Mon, Oct 29, 2012 at 11:50 AM, Nicolas Ribot > wrote: > >> Could you draw a figure ? >> >> Nicolas >> >> On 29 October 2012 11:03, Ed Linde wrote: >> > Hi All, >> > Thanks for the suggestions. >> > For 1) I will look into how ST_touches works and see if it can pick up >> all >> > the adjacent polygons to >> > the one I have. And also look into Mike's suggestion on >> ST_relate...though I >> > must admit it looks >> > more complex. >> > For 2) I will try to clarify it a bit more... its harder to do without a >> > figure :) but here goes. >> > >> > Lets say we have a point Q which is the generator of a voronoi cell. >> Now I >> > compute the >> > intersection between the voronoi cell boundaries and my triangulation >> (Set >> > of polygons) >> > using ST_intersect. Once I have these triangles.. I say pick one >> triangle T >> > that is >> > intersecting the voronoi cell boundary of Q. >> > For all the triangles adjacent to T, I need to know which triangles are >> > INSIDE the voronoi >> > boundary (closer to Q) and which adjacent triangles are just OUTSIDE the >> > voronoi >> > boundary (farther from Q). I am basically testing for a certain >> property by >> > "shrinking" the >> > voronoi cell (closer to Q) and another property when "expanding" the >> voronoi >> > cell (away from Q). >> > Just need to make this division of triangles. Haven't thought of a nice >> way >> > to do this in postgis 2.0 >> > So any suggestions would greatly help. >> > >> > Thanks, >> > Ed >> > >> > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews wrote: >> >> >> >> On 29 October 2012 21:33, Ed Linde wrote: >> >> > Hi All, >> >> > I need help with 2 hard problems. I store triangles in a table as >> >> > POLYGON. >> >> > >> >> > 1. I want to know for a given triangle, which triangles share an edge >> >> > (adjacent) with this triangle. >> >> >> >> Sounds like you have a finite element mesh with nodes and elements. >> >> You can use ST_Relate with pattern 'FF2F11212' to pick out elements >> >> that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, >> >> but only takes linear boundary overlaps. So if you have a table >> >> "elements", and you want to find ones that touch ID 567: >> >> >> >> SELECT elements.* >> >> FROM elements, elements as e >> >> WHERE e.id = 567 AND >> >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); >> >> >> >> I'm not certain about your second question. >> >> >> >> -Mike >> >> ___ >> >> 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 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] Difficult Problem with Polygons
Could you draw a figure ? Nicolas On 29 October 2012 11:03, Ed Linde wrote: > Hi All, > Thanks for the suggestions. > For 1) I will look into how ST_touches works and see if it can pick up all > the adjacent polygons to > the one I have. And also look into Mike's suggestion on ST_relate...though I > must admit it looks > more complex. > For 2) I will try to clarify it a bit more... its harder to do without a > figure :) but here goes. > > Lets say we have a point Q which is the generator of a voronoi cell. Now I > compute the > intersection between the voronoi cell boundaries and my triangulation (Set > of polygons) > using ST_intersect. Once I have these triangles.. I say pick one triangle T > that is > intersecting the voronoi cell boundary of Q. > For all the triangles adjacent to T, I need to know which triangles are > INSIDE the voronoi > boundary (closer to Q) and which adjacent triangles are just OUTSIDE the > voronoi > boundary (farther from Q). I am basically testing for a certain property by > "shrinking" the > voronoi cell (closer to Q) and another property when "expanding" the voronoi > cell (away from Q). > Just need to make this division of triangles. Haven't thought of a nice way > to do this in postgis 2.0 > So any suggestions would greatly help. > > Thanks, > Ed > > On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews wrote: >> >> On 29 October 2012 21:33, Ed Linde wrote: >> > Hi All, >> > I need help with 2 hard problems. I store triangles in a table as >> > POLYGON. >> > >> > 1. I want to know for a given triangle, which triangles share an edge >> > (adjacent) with this triangle. >> >> Sounds like you have a finite element mesh with nodes and elements. >> You can use ST_Relate with pattern 'FF2F11212' to pick out elements >> that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, >> but only takes linear boundary overlaps. So if you have a table >> "elements", and you want to find ones that touch ID 567: >> >> SELECT elements.* >> FROM elements, elements as e >> WHERE e.id = 567 AND >> ST_Relate(elements.geom, e.geom, 'FF2F11212'); >> >> I'm not certain about your second question. >> >> -Mike >> _______ >> 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
Hi All, Thanks for the suggestions. For 1) I will look into how ST_touches works and see if it can pick up all the adjacent polygons to the one I have. And also look into Mike's suggestion on ST_relate...though I must admit it looks more complex. For 2) I will try to clarify it a bit more... its harder to do without a figure :) but here goes. Lets say we have a point Q which is the generator of a voronoi cell. Now I compute the intersection between the voronoi cell boundaries and my triangulation (Set of polygons) using ST_intersect. Once I have these triangles.. I say pick one triangle T that is intersecting the voronoi cell boundary of Q. For all the triangles adjacent to T, I need to know which triangles are INSIDE the voronoi boundary (closer to Q) and which adjacent triangles are just OUTSIDE the voronoi boundary (farther from Q). I am basically testing for a certain property by "shrinking" the voronoi cell (closer to Q) and another property when "expanding" the voronoi cell (away from Q). Just need to make this division of triangles. Haven't thought of a nice way to do this in postgis 2.0 So any suggestions would greatly help. Thanks, Ed On Mon, Oct 29, 2012 at 10:15 AM, Mike Toews wrote: > On 29 October 2012 21:33, Ed Linde wrote: > > Hi All, > > I need help with 2 hard problems. I store triangles in a table as > POLYGON. > > > > 1. I want to know for a given triangle, which triangles share an edge > > (adjacent) with this triangle. > > Sounds like you have a finite element mesh with nodes and elements. > You can use ST_Relate with pattern 'FF2F11212' to pick out elements > that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, > but only takes linear boundary overlaps. So if you have a table > "elements", and you want to find ones that touch ID 567: > > SELECT elements.* > FROM elements, elements as e > WHERE e.id = 567 AND > ST_Relate(elements.geom, e.geom, 'FF2F11212'); > > I'm not certain about your second question. > > -Mike > _______ > 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] Difficult Problem with Polygons
On 29 October 2012 21:33, Ed Linde wrote: > Hi All, > I need help with 2 hard problems. I store triangles in a table as POLYGON. > > 1. I want to know for a given triangle, which triangles share an edge > (adjacent) with this triangle. Sounds like you have a finite element mesh with nodes and elements. You can use ST_Relate with pattern 'FF2F11212' to pick out elements that share the same edge. This DE-9-IM is sort-of a custom ST_Touches, but only takes linear boundary overlaps. So if you have a table "elements", and you want to find ones that touch ID 567: SELECT elements.* FROM elements, elements as e WHERE e.id = 567 AND ST_Relate(elements.geom, e.geom, 'FF2F11212'); I'm not certain about your second question. -Mike ___________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Difficult Problem with Polygons
Hi, 1. Does st_touches not work for that ? 2. What do you mean by below ? south from the line ? St_distance should help you to find the closer one from Q. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Ed Linde Date: lun. 29/10/2012 09:33 À: PostGIS Users Discussion Objet : [postgis-users] Difficult Problem with Polygons Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. 2. Then I have a line that cuts through a triangle's face and a point Q, away from this line and triangle. I would like to know amongst the neighbours of the triangle (calculated in 1), which ones fall *below* the line and closer to the point Q (where Q is the generator of a Voronoi cell). And also the set of triangles that are *above* the line and further from Q. Any suggestions as to how I can go about achieving this in postgis 2.0? Cheers, Ed <>_______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Difficult Problem with Polygons
Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. 2. Then I have a line that cuts through a triangle's face and a point Q, away from this line and triangle. I would like to know amongst the neighbours of the triangle (calculated in 1), which ones fall *below* the line and closer to the point Q (where Q is the generator of a Voronoi cell). And also the set of triangles that are *above* the line and further from Q. Any suggestions as to how I can go about achieving this in postgis 2.0? Cheers, Ed ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] k-nearest Polygons
thanks. On Sat, Oct 27, 2012 at 4:42 PM, Skye Book wrote: > http://postgis.refractions.net/docs/geometry_distance_box.html > http://postgis.refractions.net/docs/geometry_distance_centroid.html > > Both operators are insanely handy and very simple to use :) > > Skye Book > http://skyebook.net -- @sbook <https://twitter.com/sbook> > > On Oct 27, 2012, at 8:34 AM, Ed Linde wrote: > > Could you please link me to the postgis 2.0 section in the docs that talks > about > using <#> and <-> ? Those operators look quite handy to me :) > > On Sat, Oct 27, 2012 at 3:32 PM, Sandro Santilli wrote: > >> On Sat, Oct 27, 2012 at 03:27:13PM +0200, Ed Linde wrote: >> > Thanks. Just to clear things up, :point_or_poly bind variable needs a >> > "geometry" >> > type passed in to it, right? >> >> Yes >> >> --strk; >> >> > >> > Thanks, >> > Ed >> > >> > On Sat, Oct 27, 2012 at 1:57 PM, Sandro Santilli >> wrote: >> > >> > > On Sat, Oct 27, 2012 at 01:37:05PM +0200, Ed Linde wrote: >> > > >> > > > Is there a way to know for a user defined "k" what the k-nearest >> polygons >> > > > to a given polygon or point are in postgis? >> > > >> > > -- Simple answer: >> > > SELECT gid from polygons >> > > ORDER BY ST_Distance(the_geom, :point_or_poly) >> > > LIMIT :k; >> > > >> > > More complex: if you have PostGIS-2.0 and need >> > > more speed the following versions do use a spatial >> > > index, if defined on "polygons": >> > > >> > > -- k nearest to bounding box of polygons: >> > > SELECT gid FROM polygons >> > > ORDER BY the_geom <#> :point_or_poly >> > > LIMIT :k; >> > > >> > > -- k nearest to polygon center: >> > > SELECT gid FROM polygons >> > > ORDER BY the_geom <-> :point_or_poly >> > > LIMIT :k; >> > > >> > > -- k nearest to polygon shape: >> > > -- NOTE: assumes the k closest objects >> > > -- are among the k*10 objects >> > > -- closer to boundingbox >> > > -- >> > > WITH k_times_10_closer AS ( >> > > SELECT gid, the_geom FROM polygons >> > >ORDER BY the_geom <#> :point_or_poly >> > >LIMIT :k*10 >> > > ) >> > > SELECT gid from k_times_10_closer >> > > ORDER BY ST_Distance(the_geom, :point_or_poly) >> > > LIMIT :k; >> > > >> > > --strk; >> > > >> > > http://www.cartodb.com - Map, analyze and build applications with >> your >> > > data >> > > >> > >~~ http://strk.keybit.net >> > > >> > > ___________ >> > > 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 >> >> >> -- >> >> http://www.cartodb.com - Map, analyze and build applications with your >> data >> >>~~ http://strk.keybit.net >> >> ___ >> 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 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] k-nearest Polygons
http://postgis.refractions.net/docs/geometry_distance_box.html http://postgis.refractions.net/docs/geometry_distance_centroid.html Both operators are insanely handy and very simple to use :) Skye Book http://skyebook.net -- @sbook <https://twitter.com/sbook> On Oct 27, 2012, at 8:34 AM, Ed Linde wrote: Could you please link me to the postgis 2.0 section in the docs that talks about using <#> and <-> ? Those operators look quite handy to me :) On Sat, Oct 27, 2012 at 3:32 PM, Sandro Santilli wrote: > On Sat, Oct 27, 2012 at 03:27:13PM +0200, Ed Linde wrote: > > Thanks. Just to clear things up, :point_or_poly bind variable needs a > > "geometry" > > type passed in to it, right? > > Yes > > --strk; > > > > > Thanks, > > Ed > > > > On Sat, Oct 27, 2012 at 1:57 PM, Sandro Santilli > wrote: > > > > > On Sat, Oct 27, 2012 at 01:37:05PM +0200, Ed Linde wrote: > > > > > > > Is there a way to know for a user defined "k" what the k-nearest > polygons > > > > to a given polygon or point are in postgis? > > > > > > -- Simple answer: > > > SELECT gid from polygons > > > ORDER BY ST_Distance(the_geom, :point_or_poly) > > > LIMIT :k; > > > > > > More complex: if you have PostGIS-2.0 and need > > > more speed the following versions do use a spatial > > > index, if defined on "polygons": > > > > > > -- k nearest to bounding box of polygons: > > > SELECT gid FROM polygons > > > ORDER BY the_geom <#> :point_or_poly > > > LIMIT :k; > > > > > > -- k nearest to polygon center: > > > SELECT gid FROM polygons > > > ORDER BY the_geom <-> :point_or_poly > > > LIMIT :k; > > > > > > -- k nearest to polygon shape: > > > -- NOTE: assumes the k closest objects > > > -- are among the k*10 objects > > > -- closer to boundingbox > > > -- > > > WITH k_times_10_closer AS ( > > > SELECT gid, the_geom FROM polygons > > >ORDER BY the_geom <#> :point_or_poly > > >LIMIT :k*10 > > > ) > > > SELECT gid from k_times_10_closer > > > ORDER BY ST_Distance(the_geom, :point_or_poly) > > > LIMIT :k; > > > > > > --strk; > > > > > > http://www.cartodb.com - Map, analyze and build applications with > your > > > data > > > > > >~~ http://strk.keybit.net > > > > > > ___ > > > 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 > > > -- > > http://www.cartodb.com - Map, analyze and build applications with your > data > >~~ http://strk.keybit.net > > ___ > 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] k-nearest Polygons
Could you please link me to the postgis 2.0 section in the docs that talks about using <#> and <-> ? Those operators look quite handy to me :) On Sat, Oct 27, 2012 at 3:32 PM, Sandro Santilli wrote: > On Sat, Oct 27, 2012 at 03:27:13PM +0200, Ed Linde wrote: > > Thanks. Just to clear things up, :point_or_poly bind variable needs a > > "geometry" > > type passed in to it, right? > > Yes > > --strk; > > > > > Thanks, > > Ed > > > > On Sat, Oct 27, 2012 at 1:57 PM, Sandro Santilli > wrote: > > > > > On Sat, Oct 27, 2012 at 01:37:05PM +0200, Ed Linde wrote: > > > > > > > Is there a way to know for a user defined "k" what the k-nearest > polygons > > > > to a given polygon or point are in postgis? > > > > > > -- Simple answer: > > > SELECT gid from polygons > > > ORDER BY ST_Distance(the_geom, :point_or_poly) > > > LIMIT :k; > > > > > > More complex: if you have PostGIS-2.0 and need > > > more speed the following versions do use a spatial > > > index, if defined on "polygons": > > > > > > -- k nearest to bounding box of polygons: > > > SELECT gid FROM polygons > > > ORDER BY the_geom <#> :point_or_poly > > > LIMIT :k; > > > > > > -- k nearest to polygon center: > > > SELECT gid FROM polygons > > > ORDER BY the_geom <-> :point_or_poly > > > LIMIT :k; > > > > > > -- k nearest to polygon shape: > > > -- NOTE: assumes the k closest objects > > > -- are among the k*10 objects > > > -- closer to boundingbox > > > -- > > > WITH k_times_10_closer AS ( > > > SELECT gid, the_geom FROM polygons > > >ORDER BY the_geom <#> :point_or_poly > > >LIMIT :k*10 > > > ) > > > SELECT gid from k_times_10_closer > > > ORDER BY ST_Distance(the_geom, :point_or_poly) > > > LIMIT :k; > > > > > > --strk; > > > > > > http://www.cartodb.com - Map, analyze and build applications with > your > > > data > > > > > >~~ http://strk.keybit.net > > > > > > ___ > > > 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 > > > -- > > http://www.cartodb.com - Map, analyze and build applications with your > data > >~~ http://strk.keybit.net > > ___ > 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] k-nearest Polygons
On Sat, Oct 27, 2012 at 03:27:13PM +0200, Ed Linde wrote: > Thanks. Just to clear things up, :point_or_poly bind variable needs a > "geometry" > type passed in to it, right? Yes --strk; > > Thanks, > Ed > > On Sat, Oct 27, 2012 at 1:57 PM, Sandro Santilli wrote: > > > On Sat, Oct 27, 2012 at 01:37:05PM +0200, Ed Linde wrote: > > > > > Is there a way to know for a user defined "k" what the k-nearest polygons > > > to a given polygon or point are in postgis? > > > > -- Simple answer: > > SELECT gid from polygons > > ORDER BY ST_Distance(the_geom, :point_or_poly) > > LIMIT :k; > > > > More complex: if you have PostGIS-2.0 and need > > more speed the following versions do use a spatial > > index, if defined on "polygons": > > > > -- k nearest to bounding box of polygons: > > SELECT gid FROM polygons > > ORDER BY the_geom <#> :point_or_poly > > LIMIT :k; > > > > -- k nearest to polygon center: > > SELECT gid FROM polygons > > ORDER BY the_geom <-> :point_or_poly > > LIMIT :k; > > > > -- k nearest to polygon shape: > > -- NOTE: assumes the k closest objects > > -- are among the k*10 objects > > -- closer to boundingbox > > -- > > WITH k_times_10_closer AS ( > > SELECT gid, the_geom FROM polygons > >ORDER BY the_geom <#> :point_or_poly > >LIMIT :k*10 > > ) > > SELECT gid from k_times_10_closer > > ORDER BY ST_Distance(the_geom, :point_or_poly) > > LIMIT :k; > > > > --strk; > > > > http://www.cartodb.com - Map, analyze and build applications with your > > data > > > > ~~ http://strk.keybit.net > > > > _______ > > 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 -- http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] k-nearest Polygons
Thanks. Just to clear things up, :point_or_poly bind variable needs a "geometry" type passed in to it, right? Thanks, Ed On Sat, Oct 27, 2012 at 1:57 PM, Sandro Santilli wrote: > On Sat, Oct 27, 2012 at 01:37:05PM +0200, Ed Linde wrote: > > > Is there a way to know for a user defined "k" what the k-nearest polygons > > to a given polygon or point are in postgis? > > -- Simple answer: > SELECT gid from polygons > ORDER BY ST_Distance(the_geom, :point_or_poly) > LIMIT :k; > > More complex: if you have PostGIS-2.0 and need > more speed the following versions do use a spatial > index, if defined on "polygons": > > -- k nearest to bounding box of polygons: > SELECT gid FROM polygons > ORDER BY the_geom <#> :point_or_poly > LIMIT :k; > > -- k nearest to polygon center: > SELECT gid FROM polygons > ORDER BY the_geom <-> :point_or_poly > LIMIT :k; > > -- k nearest to polygon shape: > -- NOTE: assumes the k closest objects > -- are among the k*10 objects > -- closer to boundingbox > -- > WITH k_times_10_closer AS ( > SELECT gid, the_geom FROM polygons >ORDER BY the_geom <#> :point_or_poly >LIMIT :k*10 > ) > SELECT gid from k_times_10_closer > ORDER BY ST_Distance(the_geom, :point_or_poly) > LIMIT :k; > > --strk; > > http://www.cartodb.com - Map, analyze and build applications with your > data > >~~ http://strk.keybit.net > > _______ > 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] k-nearest Polygons
On Sat, Oct 27, 2012 at 01:37:05PM +0200, Ed Linde wrote: > Is there a way to know for a user defined "k" what the k-nearest polygons > to a given polygon or point are in postgis? -- Simple answer: SELECT gid from polygons ORDER BY ST_Distance(the_geom, :point_or_poly) LIMIT :k; More complex: if you have PostGIS-2.0 and need more speed the following versions do use a spatial index, if defined on "polygons": -- k nearest to bounding box of polygons: SELECT gid FROM polygons ORDER BY the_geom <#> :point_or_poly LIMIT :k; -- k nearest to polygon center: SELECT gid FROM polygons ORDER BY the_geom <-> :point_or_poly LIMIT :k; -- k nearest to polygon shape: -- NOTE: assumes the k closest objects -- are among the k*10 objects -- closer to boundingbox -- WITH k_times_10_closer AS ( SELECT gid, the_geom FROM polygons ORDER BY the_geom <#> :point_or_poly LIMIT :k*10 ) SELECT gid from k_times_10_closer ORDER BY ST_Distance(the_geom, :point_or_poly) LIMIT :k; --strk; http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net _______________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] k-nearest Polygons
Hi All, Is there a way to know for a user defined "k" what the k-nearest polygons to a given polygon or point are in postgis? Cheers, Ed _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostgreSQL HStore and PostGIS
On 26 October 2012 12:26, Blair Deaver wrote: > Can anyone comment on successful or unsuccessful attempts to implement such > a technology approach integrating PostGIS with HStore? Here are some links of interest that pair up PostGIS + HStore: http://www.stormdb.com/content/finding-dataset?destination=node%2F926 http://www.postgresonline.com/journal/archives/265-Schemas-vs.-Schemaless-structures-and-The-PostgreSQL-Type-Farm.html -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostgreSQL HStore and PostGIS
Blair, If I understood correctly you dont intend to store geometries in HStore but in the standard geometry PostGIS enabled column. Storing key/value pairs in hstore should not bring any issues. You can also index such hstore field for quick searches. In order to properly get / set data in Hstore fields, read the documentation. Since 9.1 (i think) hstore set/get are diffrent from previous versions. I guess it all comes to how large will your table be, but my first impression is you should be fine if your are just "storing" unstructured data. Hope this helped. Ricardo 2012/10/25 Stephen Frost > Blair, > > * Blair Deaver (blairdea...@gmail.com) wrote: > > With that said, I have been looking into the new capabilities available > > with PostgreSQL HStore > > Uhm.. hstore isn't exactly new. It's been around since something like > 8.0.. It is amusing how people are just now coming to see. :) > > >- Can anyone comment on successful or unsuccessful attempts to > implement > >such a technology approach integrating PostGIS with HStore? > > I've not heard of anyone putting geometries into hstore's. It'd be > possible, but realize that to put something into an hstore, it has to be > text. Also, I don't think you'd be able to create a geometry index over > geometries which are inside of an hstore.. Or at least, if you did, it > might be difficult to actually use. > > >- How would one extract data out in such a manner (I suspect a custom > >program would need to be run)? > > Not sure what you mean by this, but you'd need to query the hstore using > the key that you want and then pull the geometry out as text and cast it > to an actual geometry column to use it. If you just want the data out > in a text format, you'd run asewkt() on the geometry after you pull it > out of the hstore. > > Thanks, > > Stephen > > _______ > 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] Pg_wrapper error
On Fri, Oct 26, 2012 at 11:27 AM, José Pedro Santos wrote: > I have the executables. When I try that (only shp2pgsql before I make the > connection with pg_wrapper) I can get the help commands. But now don't > because I enable the file with pg_wrapper, how can undo that? > When you created the symlink using the ln command, it would have overwritten the executables. If you backed them up first, delete the symlink and restore the executable from backup. If you didn't back them up, you'll have to reinstall PostGIS. --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/ _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Pg_wrapper error
> Date: Fri, 26 Oct 2012 10:58:22 -0400
> From: lee.hachadooria...@gmail.com
> To: postgis-users@postgis.refractions.net
> Subject: Re: [postgis-users] Pg_wrapper error
>
> On Fri, Oct 26, 2012 at 10:51 AM, José Pedro Santos
> wrote:
> >
> > Hi,
> >
> > raster2pgsql don't work also shp2pgsql don't work (I have the Postgis 2.0
> > installed in Ubuntu 12.04). I just enable the function but seems that the
> > pg_wrapper don't work.
> >
> > I have all the raster2... and shp2... in /usr/bin but don't work when I try
> > to call them in the shell.
>
> When you say you have raster2pgsql and shp2pgsql in /usr/bin, do you
> have executables or just the symlinks you created? If you call
> shp2pgsql without arguments do you get the help commands? Can you
> output it to a file? That is instead of:
>
> shp2pgsql /home/user/Downloads/Africa_Boundaries/africa.shp
> teste2.africa | psql -d MyDatabase
>
> can you
>
> shp2pgsql /home/user/Downloads/Africa_Boundaries/africa.shp
> teste2.africa > /home/user/Downloads/import_africa.sql
>
> ?
>
> --Lee
>
> --
> Lee Hachadoorian
> Asst Professor of Geography, Dartmouth College
> http://freecity.commons.gc.cuny.edu/
I have the executables. When I try that (only shp2pgsql before I make the
connection with pg_wrapper) I can get the help commands. But now don't because
I enable the file with pg_wrapper, how can undo that?
When I try that expression I have the same error:
Error: pg_wrapper: invalid command name
Regards,
JSantos
> _______
> 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] Pg_wrapper error
On Fri, Oct 26, 2012 at 10:51 AM, José Pedro Santos wrote: > > Hi, > > raster2pgsql don't work also shp2pgsql don't work (I have the Postgis 2.0 > installed in Ubuntu 12.04). I just enable the function but seems that the > pg_wrapper don't work. > > I have all the raster2... and shp2... in /usr/bin but don't work when I try > to call them in the shell. When you say you have raster2pgsql and shp2pgsql in /usr/bin, do you have executables or just the symlinks you created? If you call shp2pgsql without arguments do you get the help commands? Can you output it to a file? That is instead of: shp2pgsql /home/user/Downloads/Africa_Boundaries/africa.shp teste2.africa | psql -d MyDatabase can you shp2pgsql /home/user/Downloads/Africa_Boundaries/africa.shp teste2.africa > /home/user/Downloads/import_africa.sql ? --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/ ___________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Pg_wrapper error
> Date: Fri, 26 Oct 2012 10:41:42 -0400 > From: lee.hachadooria...@gmail.com > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] Pg_wrapper error > > On Thu, Oct 25, 2012 at 11:26 AM, José Pedro Santos > wrote: > > Dear all, > > > > I have one problem when I try to load raster to Postgis with the script > > raster2pgsql from the shell. > > > > I have this error: Error: pg_wrapper: invalid command name > > > > Before this error (I have Postgis version 2.0) I can't use the command > > (raster2pgsql) in the shell (was not found) but i enable it with this > > expression: > > > > sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/bin/raster2pgsql > > > > After that the error about pg_wrapper appear. > > > > Any suggestion? > > > > Best Regards and thanks, > > Jose Santos > > raster2pgsql and shp2pgsql are not links to pg_wrapper. The error you > are getting is exactly what I get if I try to call pg_wrapper > directly, but raster2pgsql works because it is installed and exists as > an executable in /usr/bin. > > Remove the link to pg_wrapper figure out why raster2pgsql isn't > installed. How did you install PostGIS 2? What OS? Is shp2pgsql > installed and working? If it is, is raster2pgsql available in the same > directory? etc. > > --Lee > > -- > Lee Hachadoorian > Asst Professor of Geography, Dartmouth College > http://freecity.commons.gc.cuny.edu/ > ___ Hi, raster2pgsql don't work also shp2pgsql don't work (I have the Postgis 2.0 installed in Ubuntu 12.04). I just enable the function but seems that the pg_wrapper don't work. I have all the raster2... and shp2... in /usr/bin but don't work when I try to call them in the shell. Any idea? Regards, JSantos > 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] Pg_wrapper error
On Thu, Oct 25, 2012 at 11:26 AM, José Pedro Santos wrote: > Dear all, > > I have one problem when I try to load raster to Postgis with the script > raster2pgsql from the shell. > > I have this error: Error: pg_wrapper: invalid command name > > Before this error (I have Postgis version 2.0) I can't use the command > (raster2pgsql) in the shell (was not found) but i enable it with this > expression: > > sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/bin/raster2pgsql > > After that the error about pg_wrapper appear. > > Any suggestion? > > Best Regards and thanks, > Jose Santos raster2pgsql and shp2pgsql are not links to pg_wrapper. The error you are getting is exactly what I get if I try to call pg_wrapper directly, but raster2pgsql works because it is installed and exists as an executable in /usr/bin. Remove the link to pg_wrapper figure out why raster2pgsql isn't installed. How did you install PostGIS 2? What OS? Is shp2pgsql installed and working? If it is, is raster2pgsql available in the same directory? etc. --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/ _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] out of memory
This could be solved by ticket #826 http://trac.osgeo.org/postgis/ticket/826 Pierre > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Bborie Park > Sent: Friday, October 26, 2012 8:26 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] out of memory > > The output size makes sense since the loader split the input raster > into 100x100 tiles. > > 1591 / 100 = 15.91 ... 16 x 100 > > 1446 / 100 = 14.46 ... 15 x 100 > > So, when unioning the tiles back together, the unioned raster should > be 1600 x 1500 (don't know where you got 1450 though). > > -bborie > > On Thu, Oct 25, 2012 at 10:43 PM, Mahavir Trivedi > wrote: > > hi i split the image (tiff) into 100 X 100 tile .(RASTER IMAGE 500 MB > > input ) > > but problem occurred when i export it then output size increase. > > > > (image input size = 1591 X 1446) > > (image output size = 1600 X 1450) > > > > > > can i change blocksize of server ? if yes then how ? > > my system XP Windows 64-bit 4 GB RAM > > > > thanks > > mahavir > > > > ___________ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > -- > Bborie Park > Programmer > Center for Vectorborne Diseases > UC Davis > 530-752-8380 > bkp...@ucdavis.edu > ___________ > 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] How to eliminate small gaps produced by ST_union?
That seamed to have worked. Here is my implementation of Sandro guidelines: SELECT ST_Union(v.the_geom) FROM (SELECT ST_MakeValid(St_Snap(t.the_geom, (SELECT ST_SnapToGrid(st_collect(f.the_geom), 0.001) as the_geom FROM (SELECT (ST_DumpPoints(g.the_geom)).geom as the_geom FROM table1 as g) as f), 0.001)) as the_geom FROM table1 as t) as v; It's obviously a slow process as it implies 2 sequential reads in the table, it takes 11ms with a very simple table containing only 2 polygons, as it implies 2 sequential reads in the table. With a bigger table ( 701 polygons, 113147 vertices), it takes 762575 ms... Since the problem occurs when I do the input of new polygons, I will try to use this procedures only for the new polygons and the adjacent ones. Thank you for your help. Alexandre Neto On Fri, Oct 26, 2012 at 10:20 AM, Sandro Santilli wrote: > On Fri, Oct 26, 2012 at 10:05:29AM +0100, Alexandre Neto wrote: > > Thank you for all the answers. > > > > I did not try the topology, as I need to study its implementation a > little > > better. > > > > I did not found the st_cleanpolygon function and using (St_Buffer, 0) did > > not worked: > > > > The dilate\erode visually corrects the problem, but of course introduce a > > small rounding in the angle introducing a small overlaping with other > > adjacent polygons... > > > > I guess that the tolerance with the GEOS PrecisionModel would be nice, I > > think that is the method used by ArcGIS products to "insure" geometry > > "topology" after aggregation operations. > > > > Any more ideas? > > You may try collecting all vertices of input into a MULTIPOINT, then > ST_SnapToGrid that MULTIPOINT, then ST_Snap each of the input geometries > to the resulting MULTIPOINT, with your desired tolerance. > Finally pass the result trough ST_MakeValid to drop collapsed polygons. > > Should work fine. Let us know. > > --strk; > > http://www.cartodb.com - Map, analyze and build applications with your > data > >~~ http://strk.keybit.net > > ___ > 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] out of memory
The output size makes sense since the loader split the input raster into 100x100 tiles. 1591 / 100 = 15.91 ... 16 x 100 1446 / 100 = 14.46 ... 15 x 100 So, when unioning the tiles back together, the unioned raster should be 1600 x 1500 (don't know where you got 1450 though). -bborie On Thu, Oct 25, 2012 at 10:43 PM, Mahavir Trivedi wrote: > hi i split the image (tiff) into 100 X 100 tile .(RASTER IMAGE 500 MB > input ) > but problem occurred when i export it then output size increase. > > (image input size = 1591 X 1446) > (image output size = 1600 X 1450) > > > can i change blocksize of server ? if yes then how ? > my system XP Windows 64-bit 4 GB RAM > > thanks > mahavir > > ___________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Pg_wrapper error
Hi, Thanks for the reply but I already call raster2pgsql. In the beginning says that I don't have the command raster2pgsql available and them I just enable it, after that appear the pg_wrapper error. Now the error pass to pg_wrapper... The problem its not the raster2pgsql. Regards, Jose Santos > Date: Thu, 25 Oct 2012 09:51:39 -0700 > From: bkp...@ucdavis.edu > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] Pg_wrapper error > > You're probably going to want to call raster2pgsql directly instead of > pg_wrapper. If the problem is with pg_wrapper, you're better asking a > debian mailing list. > > So... > > /usr/bin/raster2pgsql MYRASTER MYTABLE | psql -d MYDB > > -bborie > > On 10/25/2012 08:26 AM, José Pedro Santos wrote: > > > > Dear all, > > > > I have one problem when I try to load raster to Postgis with the script > > raster2pgsql from the shell. > > > > I have this error: Error: pg_wrapper: invalid command name > > > > Before this error (I have Postgis version 2.0) I can't use the command > > (raster2pgsql) in the shell (was not found) but i enable it with this > > expression: > > sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/bin/raster2pgsql > > > > After that the error about pg_wrapper appear. > > > > Any suggestion? > > > > Best Regards and thanks, > > Jose Santos > > > > > > > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > -- > Bborie Park > Programmer > Center for Vectorborne Diseases > UC Davis > 530-752-8380 > bkp...@ucdavis.edu > ___________ > 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] How to eliminate small gaps produced by ST_union?
On Fri, Oct 26, 2012 at 10:05:29AM +0100, Alexandre Neto wrote: > Thank you for all the answers. > > I did not try the topology, as I need to study its implementation a little > better. > > I did not found the st_cleanpolygon function and using (St_Buffer, 0) did > not worked: > > The dilate\erode visually corrects the problem, but of course introduce a > small rounding in the angle introducing a small overlaping with other > adjacent polygons... > > I guess that the tolerance with the GEOS PrecisionModel would be nice, I > think that is the method used by ArcGIS products to "insure" geometry > "topology" after aggregation operations. > > Any more ideas? You may try collecting all vertices of input into a MULTIPOINT, then ST_SnapToGrid that MULTIPOINT, then ST_Snap each of the input geometries to the resulting MULTIPOINT, with your desired tolerance. Finally pass the result trough ST_MakeValid to drop collapsed polygons. Should work fine. Let us know. --strk; http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to eliminate small gaps produced by ST_union?
Thank you for all the answers. I did not try the topology, as I need to study its implementation a little better. I did not found the st_cleanpolygon function and using (St_Buffer, 0) did not worked: The dilate\erode visually corrects the problem, but of course introduce a small rounding in the angle introducing a small overlaping with other adjacent polygons... I guess that the tolerance with the GEOS PrecisionModel would be nice, I think that is the method used by ArcGIS products to "insure" geometry "topology" after aggregation operations. Any more ideas? Alexandre Neto On Fri, Oct 26, 2012 at 8:40 AM, Dave Potts wrote: > Have you considered curve smoothing or apply a small buffer around the > orginal data/ > > Sandro Santilli wrote: > > It will be _very_ slow. > > So much that you may prefer to make the topology persistent. > > > > Another approach I'd like to see implemented would be to expose a > > tolerance > > to the GEOS implemented functions. Basically exposing the GEOS > > PrecisionModel. By doing that you could specify a tolerance in the > overlay > > operations and the result should never have gaps bigger than the given > > tolerance. > > > > --strk; > > > > On Thu, Oct 25, 2012 at 10:28:19AM -0700, pcr...@pcreso.com wrote: > >> My impression is that working topologies is slow, especially with large > >> datasets - lots of computation involved. > >> > >> How effective would this be with hundreds of thousands or millions or > >> small polygons? > >> > >> Just running ST_Union() takes hours to days. > >> > >> Thanks > >> > >> Brent Wood > >> > >> --- On Fri, 10/26/12, Sandro Santilli wrote: > >> > >> From: Sandro Santilli > >> Subject: Re: [postgis-users] How to eliminate small gaps produced by > >> ST_union? > >> To: "PostGIS Users Discussion" > >> Date: Friday, October 26, 2012, 6:04 AM > >> > >> You may try to construct a topology within PostGIS-2.0, using > >> a small tolerance, and then get the geometries back. > >> > >> --strk; > >> > >> On Thu, Oct 25, 2012 at 05:54:05PM +0100, Alexandre Neto wrote: > >> > I'm trying to Aggregate\Dissolve a set of adjacent, but not touching > >> > polygons in Postgis. The geometries were created editing the Postgis > >> table > >> > in QGIS created, with snapping and avoid overlap in the layer. > >> > > >> > I used ST_Union like this: > >> > > >> > SELECT ST_Union(the_geom) > >> > FROM table; > >> > > >> > Small gaps similar to lines appear in the result. This happens when > >> the > >> > adjacent polygons do not share the exact same vertices. > >> > > >> > [image: Inline image 2] > >> > Is there a way to eliminate this "gaps" or "small angles" within > >> Postgis? > >> > > >> > I know that v.clean in GRASS can eliminate small angles, and correct > >> > topology, but that would mean I needed to copy the features to GRASS, > >> > correct them and copy it back to Postgis. > >> > > >> > Thanks in advance, > >> > > >> > Alexandre Neto > >> > >> > >> ___ > >> 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 > > > > > > -- > > > > http://www.cartodb.com - Map, analyze and build applications with your > > data > > > >~~ http://strk.keybit.net > > > > ___ > > 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to eliminate small gaps produced by ST_union?
Have you considered curve smoothing or apply a small buffer around the orginal data/ Sandro Santilli wrote: > It will be _very_ slow. > So much that you may prefer to make the topology persistent. > > Another approach I'd like to see implemented would be to expose a > tolerance > to the GEOS implemented functions. Basically exposing the GEOS > PrecisionModel. By doing that you could specify a tolerance in the overlay > operations and the result should never have gaps bigger than the given > tolerance. > > --strk; > > On Thu, Oct 25, 2012 at 10:28:19AM -0700, pcr...@pcreso.com wrote: >> My impression is that working topologies is slow, especially with large >> datasets - lots of computation involved. >> >> How effective would this be with hundreds of thousands or millions or >> small polygons? >> >> Just running ST_Union() takes hours to days. >> >> Thanks >> >> Brent Wood >> >> --- On Fri, 10/26/12, Sandro Santilli wrote: >> >> From: Sandro Santilli >> Subject: Re: [postgis-users] How to eliminate small gaps produced by >> ST_union? >> To: "PostGIS Users Discussion" >> Date: Friday, October 26, 2012, 6:04 AM >> >> You may try to construct a topology within PostGIS-2.0, using >> a small tolerance, and then get the geometries back. >> >> --strk; >> >> On Thu, Oct 25, 2012 at 05:54:05PM +0100, Alexandre Neto wrote: >> > I'm trying to Aggregate\Dissolve a set of adjacent, but not touching >> > polygons in Postgis. The geometries were created editing the Postgis >> table >> > in QGIS created, with snapping and avoid overlap in the layer. >> > >> > I used ST_Union like this: >> > >> > SELECT ST_Union(the_geom) >> > FROM table; >> > >> > Small gaps similar to lines appear in the result. This happens when >> the >> > adjacent polygons do not share the exact same vertices. >> > >> > [image: Inline image 2] >> > Is there a way to eliminate this "gaps" or "small angles" within >> Postgis? >> > >> > I know that v.clean in GRASS can eliminate small angles, and correct >> > topology, but that would mean I needed to copy the features to GRASS, >> > correct them and copy it back to Postgis. >> > >> > Thanks in advance, >> > >> > Alexandre Neto >> >> >> ___ >> 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 > > > -- > > http://www.cartodb.com - Map, analyze and build applications with your > data > >~~ http://strk.keybit.net > > ___ > 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] How to eliminate small gaps produced by ST_union?
It will be _very_ slow. So much that you may prefer to make the topology persistent. Another approach I'd like to see implemented would be to expose a tolerance to the GEOS implemented functions. Basically exposing the GEOS PrecisionModel. By doing that you could specify a tolerance in the overlay operations and the result should never have gaps bigger than the given tolerance. --strk; On Thu, Oct 25, 2012 at 10:28:19AM -0700, pcr...@pcreso.com wrote: > My impression is that working topologies is slow, especially with large > datasets - lots of computation involved. > > How effective would this be with hundreds of thousands or millions or small > polygons? > > Just running ST_Union() takes hours to days. > > Thanks > > Brent Wood > > --- On Fri, 10/26/12, Sandro Santilli wrote: > > From: Sandro Santilli > Subject: Re: [postgis-users] How to eliminate small gaps produced by ST_union? > To: "PostGIS Users Discussion" > Date: Friday, October 26, 2012, 6:04 AM > > You may try to construct a topology within PostGIS-2.0, using > a small tolerance, and then get the geometries back. > > --strk; > > On Thu, Oct 25, 2012 at 05:54:05PM +0100, Alexandre Neto wrote: > > I'm trying to Aggregate\Dissolve a set of adjacent, but not touching > > polygons in Postgis. The geometries were created editing the Postgis table > > in QGIS created, with snapping and avoid overlap in the layer. > > > > I used ST_Union like this: > > > > SELECT ST_Union(the_geom) > > FROM table; > > > > Small gaps similar to lines appear in the result. This happens when the > > adjacent polygons do not share the exact same vertices. > > > > [image: Inline image 2] > > Is there a way to eliminate this "gaps" or "small angles" within Postgis? > > > > I know that v.clean in GRASS can eliminate small angles, and correct > > topology, but that would mean I needed to copy the features to GRASS, > > correct them and copy it back to Postgis. > > > > Thanks in advance, > > > > Alexandre Neto > > > ___________ > 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 -- http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] out of memory
hi i split the image (tiff) into 100 X 100 tile .(RASTER IMAGE 500 MB input ) but problem occurred when i export it then output size increase. (image input size = 1591 X 1446) (image output size = 1600 X 1450) can i change blocksize of server ? if yes then how ? my system XP Windows 64-bit 4 GB RAM thanks mahavir ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostgreSQL HStore and PostGIS
Blair, * Blair Deaver (blairdea...@gmail.com) wrote: > With that said, I have been looking into the new capabilities available > with PostgreSQL HStore Uhm.. hstore isn't exactly new. It's been around since something like 8.0.. It is amusing how people are just now coming to see. :) >- Can anyone comment on successful or unsuccessful attempts to implement >such a technology approach integrating PostGIS with HStore? I've not heard of anyone putting geometries into hstore's. It'd be possible, but realize that to put something into an hstore, it has to be text. Also, I don't think you'd be able to create a geometry index over geometries which are inside of an hstore.. Or at least, if you did, it might be difficult to actually use. >- How would one extract data out in such a manner (I suspect a custom >program would need to be run)? Not sure what you mean by this, but you'd need to query the hstore using the key that you want and then pull the geometry out as text and cast it to an actual geometry column to use it. If you just want the data out in a text format, you'd run asewkt() on the geometry after you pull it out of the hstore. Thanks, Stephen signature.asc Description: Digital signature _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostgreSQL HStore and PostGIS
Hello, I have a need to design and develop an application which handles a wide variety of possible variations for a database schema. I am looking into a number of options including popular NoSQL databases. I do keep coming back to PostgreSQL and PostGIS since I need to deal with geometries in a more complex manner than either MongoDB or CouchDB Geospatial Indexing offer. With that said, I have been looking into the new capabilities available with PostgreSQL HStore module<https://postgres.heroku.com/blog/past/2012/3/14/introducing_keyvalue_data_storage_in_heroku_postgres/> as a possible alternative to be able to store geometries with PostGIS and tie additional key/value pairs in a hstore data type column. - Can anyone comment on successful or unsuccessful attempts to implement such a technology approach integrating PostGIS with HStore? - How would one extract data out in such a manner (I suspect a custom program would need to be run)? I do realize HStore is very new on the scene, but curious if this environment is something that will even work with PostGIS. Thanks! Cheers, Blair ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Finding extra points
Sure, use linear referencing functions[1] to interpolate along a line between the two input points. For example, to find 10 points in-between 'POINT(2 8)' and 'POINT(3 5)': select ST_AsText(ST_Line_Interpolate_Point(ST_MakeLine(ST_MakePoint(2, 8), ST_MakePoint(3, 5)), f::float/10)) from generate_series(0,10) as f; Also, if you have a Z or M coordinate to also interpolate, you can add it as the third and fourth argument of ST_MakeLine, and you will see the interpolated result. These extra coordinate dimensions, for example, could be an attribute quantity from your source point data. Keep in mind, these are linear interpolation techniques. -Mike [1] http://postgis.refractions.net/documentation/manual-2.0/reference.html#Linear_Referencing On 26 October 2012 00:15, Jeff Lake wrote: > Greetings.. > Is there a function or combination of functions in postGIS-2 that will help > calculate > points in between 2 given. > > I have imported a point shape file containing the National Hurricane > Center's Watch/Warning breakpoints > when they issue the warnings it contains at least 2 locations from this > shape file. > > is there a way to calculate what points from the shape file would be in > between the 2 given?? > > -- > Jeff Lake > MichiganWxSystem > AllisonHouse > GRLevelXStuff > > ___ > 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] How to eliminate small gaps produced by ST_union?
If you don't mind altering your other edges somewhat in the process, dilate/erode will blow those away, http://blog.opengeo.org/2010/11/22/removing-complexities/ P. On Thu, Oct 25, 2012 at 10:44 AM, Bo Victor Thomsen wrote: > > Try to use function st_cleanpolygon or st_buffer with a radius of 0.0. > > Regards > Bo Victor Thomsen > Aestas-GIS > Denmark > > Den 25-10-2012 18:54, Alexandre Neto skrev: > > I'm trying to Aggregate\Dissolve a set of adjacent, but not touching polygons > in Postgis. The geometries were created editing the Postgis table in QGIS > created, with snapping and avoid overlap in the layer. > > I used ST_Union like this: > > SELECT ST_Union(the_geom) > FROM table; > > Small gaps similar to lines appear in the result. This happens when the > adjacent polygons do not share the exact same vertices. > > > Is there a way to eliminate this "gaps" or "small angles" within Postgis? > > I know that v.clean in GRASS can eliminate small angles, and correct > topology, but that would mean I needed to copy the features to GRASS, correct > them and copy it back to Postgis. > > Thanks in advance, > > Alexandre Neto > > > > > _______ > 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to eliminate small gaps produced by ST_union?
Try to use function st_cleanpolygon or st_buffer with a radius of 0.0. Regards Bo Victor Thomsen Aestas-GIS Denmark Den 25-10-2012 18:54, Alexandre Neto skrev: I'm trying to Aggregate\Dissolve a set of adjacent, but not touching polygons in Postgis. The geometries were created editing the Postgis table in QGIS created, with snapping and avoid overlap in the layer. I used ST_Union like this: SELECT ST_Union(the_geom) FROM table; Small gaps similar to lines appear in the result. This happens when the adjacent polygons do not share the exact same vertices. Is there a way to eliminate this "gaps" or "small angles" within Postgis? I know that v.clean in GRASS can eliminate small angles, and correct topology, but that would mean I needed to copy the features to GRASS, correct them and copy it back to Postgis. Thanks in advance, Alexandre Neto ___________ 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] How to eliminate small gaps produced by ST_union?
My impression is that working topologies is slow, especially with large datasets - lots of computation involved. How effective would this be with hundreds of thousands or millions or small polygons? Just running ST_Union() takes hours to days. Thanks Brent Wood --- On Fri, 10/26/12, Sandro Santilli wrote: From: Sandro Santilli Subject: Re: [postgis-users] How to eliminate small gaps produced by ST_union? To: "PostGIS Users Discussion" Date: Friday, October 26, 2012, 6:04 AM You may try to construct a topology within PostGIS-2.0, using a small tolerance, and then get the geometries back. --strk; On Thu, Oct 25, 2012 at 05:54:05PM +0100, Alexandre Neto wrote: > I'm trying to Aggregate\Dissolve a set of adjacent, but not touching > polygons in Postgis. The geometries were created editing the Postgis table > in QGIS created, with snapping and avoid overlap in the layer. > > I used ST_Union like this: > > SELECT ST_Union(the_geom) > FROM table; > > Small gaps similar to lines appear in the result. This happens when the > adjacent polygons do not share the exact same vertices. > > [image: Inline image 2] > Is there a way to eliminate this "gaps" or "small angles" within Postgis? > > I know that v.clean in GRASS can eliminate small angles, and correct > topology, but that would mean I needed to copy the features to GRASS, > correct them and copy it back to Postgis. > > Thanks in advance, > > Alexandre Neto ___ 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] How to eliminate small gaps produced by ST_union?
You may try to construct a topology within PostGIS-2.0, using a small tolerance, and then get the geometries back. --strk; On Thu, Oct 25, 2012 at 05:54:05PM +0100, Alexandre Neto wrote: > I'm trying to Aggregate\Dissolve a set of adjacent, but not touching > polygons in Postgis. The geometries were created editing the Postgis table > in QGIS created, with snapping and avoid overlap in the layer. > > I used ST_Union like this: > > SELECT ST_Union(the_geom) > FROM table; > > Small gaps similar to lines appear in the result. This happens when the > adjacent polygons do not share the exact same vertices. > > [image: Inline image 2] > Is there a way to eliminate this "gaps" or "small angles" within Postgis? > > I know that v.clean in GRASS can eliminate small angles, and correct > topology, but that would mean I needed to copy the features to GRASS, > correct them and copy it back to Postgis. > > Thanks in advance, > > Alexandre Neto ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to eliminate small gaps produced by ST_union?
I'm trying to Aggregate\Dissolve a set of adjacent, but not touching polygons in Postgis. The geometries were created editing the Postgis table in QGIS created, with snapping and avoid overlap in the layer. I used ST_Union like this: SELECT ST_Union(the_geom) FROM table; Small gaps similar to lines appear in the result. This happens when the adjacent polygons do not share the exact same vertices. [image: Inline image 2] Is there a way to eliminate this "gaps" or "small angles" within Postgis? I know that v.clean in GRASS can eliminate small angles, and correct topology, but that would mean I needed to copy the features to GRASS, correct them and copy it back to Postgis. Thanks in advance, Alexandre Neto <>___________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Pg_wrapper error
You're probably going to want to call raster2pgsql directly instead of pg_wrapper. If the problem is with pg_wrapper, you're better asking a debian mailing list. So... /usr/bin/raster2pgsql MYRASTER MYTABLE | psql -d MYDB -bborie On 10/25/2012 08:26 AM, José Pedro Santos wrote: > > Dear all, > > I have one problem when I try to load raster to Postgis with the script > raster2pgsql from the shell. > > I have this error: Error: pg_wrapper: invalid command name > > Before this error (I have Postgis version 2.0) I can't use the command > (raster2pgsql) in the shell (was not found) but i enable it with this > expression: > sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/bin/raster2pgsql > > After that the error about pg_wrapper appear. > > Any suggestion? > > Best Regards and thanks, > Jose Santos > > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] out of memory
You may want to increase your shared_buffer to 25% of your system's available memory. You really should tile your raster. -bborie On 10/25/2012 02:19 AM, Mahavir Trivedi wrote: > i have 4 GB RAM WINDOWS XP 64-bit > > shared_buffer:512 MB checkpoint_segment 32 > > work_mem;5MB > > i wish to import 540 MB tif image into database postgis 2.0 . without tile . > > i got success test.sql file. but error occured when psql -d dbname -f test.sql > > error : String is 978678801 byte too long for encoding conversion > > pls give any suitable solution > > with thanks > mahavir > ___________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Pg_wrapper error
Dear all, I have one problem when I try to load raster to Postgis with the script raster2pgsql from the shell. I have this error: Error: pg_wrapper: invalid command name Before this error (I have Postgis version 2.0) I can't use the command (raster2pgsql) in the shell (was not found) but i enable it with this expression: sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/bin/raster2pgsql After that the error about pg_wrapper appear. Any suggestion? Best Regards and thanks, Jose Santos ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Reducing the number of points in a GPS track
On Oct 24, 2012, at 2:29 PM, Alexandre Saunier wrote: Hi Alex, > A bit like the "simplify,count=<...>" filter in GPSBabel: > http://www.gpsbabel.org/htmldoc-development/filter_simplify.html > > Is there a way to do that kind of simplification with PostGIS? A way could be to : - Compute length of your road with ST_Length - Use ST_Line_Interpolate_Point to compute each point on your line - Aggregate all points computed in a new simplified line with ST_MakeLine HTH, O. _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Finding extra points
On Thu, Oct 25, 2012 at 07:15:20AM -0400, Jeff Lake wrote: > Greetings.. > Is there a function or combination of functions in postGIS-2 that > will help calculate > points in between 2 given. > > I have imported a point shape file containing the National Hurricane > Center's Watch/Warning breakpoints > when they issue the warnings it contains at least 2 locations from > this shape file. > > is there a way to calculate what points from the shape file would be > in between the 2 given?? You could draw a line between the two points and find the points which are within a given distance from them. See ST_MakeLine and ST_DWithin. --strk; http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net _______________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Finding extra points
Greetings.. Is there a function or combination of functions in postGIS-2 that will help calculate points in between 2 given. I have imported a point shape file containing the National Hurricane Center's Watch/Warning breakpoints when they issue the warnings it contains at least 2 locations from this shape file. is there a way to calculate what points from the shape file would be in between the 2 given?? -- *Jeff Lake* MichiganWxSystem <http://www.michiganwxsystem.com> AllisonHouse <http://www.allisonhouse.com> GRLevelXStuff <http://www.grlevelxstuff.com> _______ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] out of memory
i have 4 GB RAM WINDOWS XP 64-bit shared_buffer:512 MB checkpoint_segment 32 work_mem;5MB i wish to import 540 MB tif image into database postgis 2.0 . without tile . i got success test.sql file. but error occured when psql -d dbname -f test.sql error : String is 978678801 byte too long for encoding conversion pls give any suitable solution with thanks mahavir ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] out of memory error : String too long for converting encoding
i have 4 GB RAM WINDOWS XP 64-bit shared_buffer:512 MB checkpoint_segment 32 work_mem;5MB i wish to import 540 MB tif image into database postgis 2.0 . without tile . i got success test.sql file. but error occured when psql -d dbname -f test.sql error : String is 978678801 byte too long for encoding conversion pls give any suitable solution with thanks mahavir ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Reducing the number of points in a GPS track
Hi Alexandre, perhaps you could run ST_Simplify in a function with a while loop testing over ST_NPoints: CREATE OR REPLACE FUNCTION simplify_npoints(geometry, integer) RETURNS geometry AS $BODY$ DECLARE InGeom alias for $1; maxpoints alias for $2; npoints integer; tolerance float; outGeom geometry; Begin npoints:=ST_NPoints(InGeom); outGeom:=InGeom; tolerance:=0.0; while npoints > maxpoints loop tolerance:=tolerance + 0.001; outGeom:=ST_Simplify(InGeom, tolerance); npoints:=ST_NPoints(outGeom); end loop; return outGeom; End; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION simplify_npoints(geometry, integer) OWNER TO postgres; I don't know if it will work (didn't test). And perhaps bigger iteration steps would be sufficient, too... Good luck! Regards, Birgit. Am 24.10.2012 14:29, schrieb Alexandre Saunier: Hello. I would like to know if some tools are available in PostGIS to simplify a linestring to a given maximum number of points. A bit like the "simplify,count=<...>" filter in GPSBabel: http://www.gpsbabel.org/htmldoc-development/filter_simplify.html ST_Simplify is close to what I need but I don't know in advance what tolerance to use, only the final maximal number of points. Is there a way to do that kind of simplification with PostGIS? Thanks! Alexandre ___________ 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
