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 ri...@wsi.com 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 ri...@wsi.com 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] [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 s...@keybit.net 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 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 ri...@wsi.com 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] 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 dougc...@gmail.com 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
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 bo.victor.thom...@gmail.com 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] identify rows in a shapefile that have illegal characters for UTF-8 encoding
On Mon, Oct 22, 2012 at 1:01 PM, Mark Volz markv...@co.lyon.mn.us wrote: I am trying to load my parcels into PostGIS, which will eventually be consumed by MapServer, and ArcGIS. Initially when loaded my data I received a warning that I should change my encoding from UTF-8 to LATIN1. How did you change your encoding? In your database, or in your data load? If you just ran shp2pgsql -W LATIN1 shpfile.shp tblename Then the non-ASCII characters in your dbf file would have been transcoded to UTF8 during the load and landed nicely in the database with the right UTF code points. Doing so allowed me to load data into PostGIS however, I could not consume the data in ArcGIS. This seems fishy. If your database is UTF and you load using the -W flag as above, everything is pretty bog standard and ArcGIS should be able to read it fine (particularly since the libpq library does all the transcoding for client apps! ArcGIS doesn't even have to think about transcoding, just declare the encoding it desires!) How can I find out which rows in my shapefile have illegal characters for UTF-8 encoding? There are no illegal character for UTF, UTF can represent any and all characters (and does). There's something else going on P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [slightly off-topic] Question on build a C address parse for an embedded geocoder
You can stuff things into an upper memory context, but I'm not sure how wise that would be. I does however seem to be the only reasonable approach to getting things to last much longer than a statement. P. On Sat, Oct 20, 2012 at 9:12 PM, Stephen Woodbridge wood...@swoodbridge.com wrote: Hi Dev's, I am interested in writing an address standardized in C that could be callable from SQL. I understand the basics of doing this from supporting pgRouting and writing some additional commands. It would get used something like: select * from standardize(address, city, state, country, postcode); select * from standardize(address_one_line); and would return a standardized set of fields like: house_num, street, city, state, country, postcode. These could be then used to create standardized reference table or it could be passed into the geocoder that would search the standardized reference table. What I am struggling with is how to best initial the address parser/standardize. The concept I have in mind is to have some tables that represent the lexicon, gazetteer, parsing rules, etc. This data could be specific to country and/or country-state. I could be fairly small or quite large. For example, there are about 40K unique city names based on the USPS zipcodes and about 7K of them have duplicate standardizations based on state. On the one hand I can read these tables on every request and build the internal structures, parse the request, and throw out the internal structures. Basically once the reference source records have been standardized you should not be changing the above tables because you want to standardize future search requests based on the same rules that the reference road segments were standardized. And ideally you do not want to spend the time to rebuild these internal structures on every search request. So is there a mechanism for building some internal data and holding on to it between requests. I suppose I could store it in a blob, but it would then need to be de-toasted on every search request. Maybe, I'm this is an non-issue, but it seems to impact the design depending on what options I might have and how they are implemented and accessed from the code. Thoughts? Thanks for any help or suggestions, -Steve W ___ 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] Display GEOGRAPHY polygons crossing date line
GEarth cannot display geography *POLYGONS* that cross the dateline. It can display linestrings just fine. P. On Tue, Oct 16, 2012 at 2:54 AM, iam_gilbey simonbolivar...@gmail.com wrote: Thanks for that info - it's something else to consider. Conclusion: Google Earth cannot display GEOGRAPHY data that crosses that date line, without a hack such as making a table of split geographies. Does anyone disagree? (please disagree...!) Thanks, James On 12 October 2012 17:47, Paul Ramsey-4 [via PostGIS] [hidden email] wrote: Even if you split the shapes, GEarth renders polygons in a non-normal kind of way which you might find unacceptable http://blog.opengeo.org/2010/08/10/shape-of-a-polygon/ The quick fix is to render them as lines, not polygons. there's no fix to get them to fill using great circle edges, GEarth just doesn't work that way. P. On Fri, Oct 12, 2012 at 4:03 AM, iam_gilbey [hidden email] wrote: I have many polygons, including several that cross the date line. They are stored as GEOGRAPHY and work perfectly with PostGIS v2.0.1, PostgreSQL 9.1.3 on windows XP. I can serve this data using Geoserver (v2.1), and view it using open layers, where the polygons crossing the date line render correctly. I cannot however convert the data into KML (viewed in Google Earth 5.1), or view it in Quantum GIS (v1.7), without the date line polygons being wrapped around the earth. I appreciate that this isn't strictly a postGIS problem, however being faced with the stark choice between splitting my data about the date line (inelegant) or not using Google Earth (unacceptable to users), I don't know where else to turn! Do you know of: a) a way to get the data to render correctly in Google Earth; or b) where else I should ask? Thanks! -- View this message in context: http://postgis.17.n6.nabble.com/Display-GEOGRAPHY-polygons-crossing-date-line-tp5000443.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list [hidden email] http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list [hidden email] http://postgis.refractions.net/mailman/listinfo/postgis-users If you reply to this email, your message will be added to the discussion below: http://postgis.17.n6.nabble.com/Display-GEOGRAPHY-polygons-crossing-date-line-tp5000443p5000464.html To unsubscribe from Display GEOGRAPHY polygons crossing date line, click here. NAML View this message in context: Re: Display GEOGRAPHY polygons crossing date line 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
Re: [postgis-users] Display GEOGRAPHY polygons crossing date line
Even if you split the shapes, GEarth renders polygons in a non-normal kind of way which you might find unacceptable http://blog.opengeo.org/2010/08/10/shape-of-a-polygon/ The quick fix is to render them as lines, not polygons. there's no fix to get them to fill using great circle edges, GEarth just doesn't work that way. P. On Fri, Oct 12, 2012 at 4:03 AM, iam_gilbey simonbolivar...@gmail.com wrote: I have many polygons, including several that cross the date line. They are stored as GEOGRAPHY and work perfectly with PostGIS v2.0.1, PostgreSQL 9.1.3 on windows XP. I can serve this data using Geoserver (v2.1), and view it using open layers, where the polygons crossing the date line render correctly. I cannot however convert the data into KML (viewed in Google Earth 5.1), or view it in Quantum GIS (v1.7), without the date line polygons being wrapped around the earth. I appreciate that this isn't strictly a postGIS problem, however being faced with the stark choice between splitting my data about the date line (inelegant) or not using Google Earth (unacceptable to users), I don't know where else to turn! Do you know of: a) a way to get the data to render correctly in Google Earth; or b) where else I should ask? Thanks! -- View this message in context: http://postgis.17.n6.nabble.com/Display-GEOGRAPHY-polygons-crossing-date-line-tp5000443.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
Re: [postgis-users] Error loading Postgis into ArcGIS as a Query Layer - The specified coordinate exceeds the valid coordinate range
On Fri, Oct 12, 2012 at 9:03 AM, Mark Volz markv...@co.lyon.mn.us wrote: One potential issue is that technically our county falls under UTM 14 and 15. However, since the majority of the state falls inside of UTM 15, many WMS services are published only for UTM 15. Therefore I don't know if ArcGIS is sending an error The specified coordinate exceeds the valid coordinate range due to the fact that the western edge of the county is in UTM 14, and not UTM 15, or if there is another issue that I need to fix. Occams razor states that you have the answer. The error message is very consistent with the situation you are describing. Use a projection that is valid for your data extent and things should be fine. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Low importance question.... using video cards
I have talked to a few folks who have done experiments with this approach, and the consensus so far is that it adds a lot of complexity, that the promises of hardware-independence from tools like OpenCLI are not truly being met, and that the performance boosts are not as big as one might hope/wish. So, currently shelved and waiting for a new era: I would say having a reliable GPU abstraction library for multi-vendor support is a bar which must be met before spending any serious effort on the work. P. On Fri, Oct 12, 2012 at 10:14 AM, Maxime Poulin mpou...@expedia.com wrote: Hi all, was simply wondering. Could it be possible that in the future, PostGIS take advantage of available hardware like video-cards to perform geo calculations ? I mean, representing a few thousand polygons and performing spatial calculations is something nVidia or ATI are doing pretty well. Plus, for all of us who were gamers and who now have teens are already paying for all of this science :) Max. ___ 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] Geographies, geometries, international date line
On Thu, Oct 11, 2012 at 12:12 AM, Nicolas Ribot nicolas.ri...@gmail.com wrote: Hi Maxime, Yes indeed, the new geography type take advantage of a very fast Gist index,often leading to queries faster than their geometry equivalent (see Paul Ramsey's blog about this new type). St_distance might also benefit from this new code. The st_distance code for geography has been sped up, but only for 2.1, not in the 2.0 series. Concerning polygons crossind dateline, yes you will have to cut them in 2 parts (trigger could be a good option for that) Not if your table is using the geography type. Geography doesn't care about datelines or poles, it laughs in the faces of cartesian singularities. Finally, yes, this list is the right place to discuss about best practices and DB optimization. Nicolas On 10 October 2012 22:27, Maxime Poulin mpou...@expedia.com wrote: Hi everyone. And so this is my first post to this mailing list, and since I registered, I haven’t seen so many messages so I hope the level of this email is up to this list’s standards. Here is the situation. At Expedia, we are working on upgrading our geo system and that implies migrating to PostGIS. During my tests, I did the following: I have two tables for regions, one, say region_geometry, keeps the polygon as a geometry(4326). The second one, say region_geography, keeps the polygon of a region as a Geography(4326). We also have other tables mapping hotels and points of interests, both storing their location as a geography point(4326). The test case is to find everything that is within a region. In other words, find all hotels and points of interests that are within a given region’s polygon. The first test uses the region_geometry table, and the query uses ST_Within function (using geometries and the operator). The second test uses the region_geography table, and the query uses ST_Intersects function (using geographies). I was expecting the geometry version to be much faster, but I actually find that performance is somewhat the same. Both queries use the special index, perform the index scan and so on. So how can this be possible, how can I be sure that geometries are actually faster than geographies ? The main issue with these two tests is regarding polygons crossing the international date line. So suppose I have a polygon that maps the Fiji islands. That polygon will cross the international date line. The geography method works fine, but the polygon, when used in geometry world, is actually a ribbon around the planet. I absolutely understand the reason, but if I really want to stay in the geometry world, how can I work around this problem ? Do I really have to split my polygon to create 2 polygons not crossing the line ? Do I have to filter my requests, find if the polygon crosses the date line and if so, “revert” to geographic calculations ? I have somewhat the same questions regarding distance calculations. In geometry world, if I want all the hotels in a 200 meters radius of a given point, I would use something like ST_Distance_spheroid(p1, p2) 200. In geography, I would use ST_DWithin(p1, p2, 200). My tests shows that STD_Within (geography, geography) is most of the time faster than st_distance(p1, p2) with or without spheroid. Again, in both cases, the spatial indexes are used. I have many quite precise and edgy questions regarding good practices and performance tuning for PostGIS. I would like to provide samples and discuss around them, see what I / we can find. Is this list the right place to ask ? Maxime Poulin. ___ 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] Not what I was expecting....
Find the .prj file next to your .shp file and plug it into here http://prj2epsg.org/search P On Thu, Oct 11, 2012 at 4:38 PM, Jeff Lake j...@michiganwxsystem.com wrote: I obtained a shape file from Environment Canada, imported it into a postGIS enabled database with shp2pgsql with no problem. queried the table and noticed the SRID was -1, reset it with UpdateGeometrySRID to 4326 now when query the table I get the following (had the same results before updating the SRID) SELECT ST_AsText(the_geom) FROM canada WHERE name LIKE 'Caledon' MULTIPOLYGON(((7185795.71819089 960058.015757074,7186925.78661135 958323.504835823,7187144.84742271 continues on I was expecting Lat/Long I *think* this is meter's ?? anybody care to guess ?? and if what needs to be adjusted and where -- 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] Geocoding using www_fdw and Google APIs
Wow, fdw_www, that's even crazier than my idea... http://blog.opengeo.org/2012/04/20/http-for-postgresql/ However, my post includes a working example of geocoding off of Google. It's still a crazy scheme, running a blocking request (curl HTTP call) inside your database transaction. P. On Mon, Oct 8, 2012 at 10:15 PM, Jeremy jeremy...@gmail.com wrote: Hi all, I've got a question on how to define a server foreign table to pull back latitude and longitude from a street address. I've been working from examples like twitter or google searches - all of which return a fairly flat data structure. The structure of the geocode return is quite different, however. For instance, it contains a field 'geometry' which in turn contains a field 'location' which finally contains values for 'lat' and 'long'. The only information I'm interested in is the geometry:location:lat, geometry:location:long and the geometry:locaiton_type I'm not sure how to structure the fields in the foreign table www_fdw_google_geocode so that this will work. Any advice would be greatly appreciated. Please see end of message for code, command and sample xml response. Thanks in advance, Jeremy. -Code I have so far-- DROP EXTENSION IF EXISTS www_fdw CASCADE; CREATE EXTENSION www_fdw; CREATE SERVER www_fdw_server_google_geocode FOREIGN DATA WRAPPER www_fdw OPTIONS (uri 'http://maps.google.com/maps/api/geocode/xml'); CREATE USER MAPPING FOR current_user SERVER www_fdw_server_google_geocode; CREATE FOREIGN TABLE www_fdw_google_geocode ( /* parameters used in request */ sensor text, address text, /* fields in response */ // /* NOT SURE HOW TO STRUCTURE THIS PART OF THE TABLE */ // /* The response data is not flat. Please see sample data at end */ ) SERVER www_fdw_server_google_geocode; -/Code I have so far-- -Command I intend to use-- postgres=# SELECT latitude, longitude, location_type from www_fdw_google_geocode where sensor='false' AND address='2 Some street, somewhere, somewhereville' LIMIT 1; -/Command I intend to use- -Sample response from geocoding web service-- ?xml version=1.0 encoding=UTF-8? GeocodeResponse statusOK/status result typestreet_address/type formatted_address2 Southbank Blvd, Southbank VIC 3006, Australia/formatted_address address_component long_name2/long_name short_name2/short_name typestreet_number/type /address_component address_component long_nameSouthbank Blvd/long_name short_nameSouthbank Blvd/short_name typeroute/type /address_component address_component long_nameSouthbank/long_name short_nameSouthbank/short_name typelocality/type typepolitical/type /address_component address_component long_nameVictoria/long_name short_nameVIC/short_name typeadministrative_area_level_1/type typepolitical/type /address_component address_component long_nameAustralia/long_name short_nameAU/short_name typecountry/type typepolitical/type /address_component address_component long_name3006/long_name short_name3006/short_name typepostal_code/type /address_component geometry location lat-37.8224465/lat lng144.9630868/lng /location location_typeROOFTOP/location_type viewport southwest lat-37.8237955/lat lng144.9617378/lng /southwest northeast lat-37.8210975/lat lng144.9644358/lng /northeast /viewport /geometry partial_matchtrue/partial_match /result [possibly more results after the first - we only want the first one] /GeocodeResponse -/Sample response from geocoding web service- ___ 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] ST_StartPoint() for MultiLineStrings
When ST_NumGeometries == 1, you can run ST_StartPoint on ST_GeometryN(geom,1). Where ST_NumGeometries 1 you are in interpretation land. What's the start point of a geometry with two lines? P. On Tue, Sep 25, 2012 at 1:38 PM, David Quinn daithiqu...@gmail.com wrote: As ST_StartPoint( multilinestring ) returns NULL for PostGIS 2.0 (http://postgis.refractions.net/docs/ST_StartPoint.html ), what approach can be used to identify the start and end points of a multilinestring? Thanks, David ___ 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] ST_StartPoint() for MultiLineStrings
This is actually one of those places where I wonder if an answer-returning approach would be better or not. One interpretation: ST_StartPoint(MultiLineString) - MultiPoint Which implies ST_ExteriorRing(Multipolygon) - MultiLineString All sorts of fun. Question is: is this an improvement... still requires handling the issues of N 1, somehow or other. P. On Tue, Sep 25, 2012 at 2:08 PM, Paul Ramsey pram...@opengeo.org wrote: When ST_NumGeometries == 1, you can run ST_StartPoint on ST_GeometryN(geom,1). Where ST_NumGeometries 1 you are in interpretation land. What's the start point of a geometry with two lines? P. On Tue, Sep 25, 2012 at 1:38 PM, David Quinn daithiqu...@gmail.com wrote: As ST_StartPoint( multilinestring ) returns NULL for PostGIS 2.0 (http://postgis.refractions.net/docs/ST_StartPoint.html ), what approach can be used to identify the start and end points of a multilinestring? Thanks, David ___ 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] downgrade from PostGis 1.5.5. to 1.5.1
On Mon, Sep 24, 2012 at 12:50 AM, Johann Eggers johann.egg...@tomtom.com wrote: Hi, Would a downgrade be as simple as copying over the old library from PostGis 1.5.1? Is there a need for replacing somehow the spatial functions in a similar way it’s been done during the softupgrade? Copying the library into place and running the minor upgrade script from the old version should cause everything to be old again. P ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Create Linestrings from multiple Points stored in other table
Your query looks fine. You know the geometry field is empty how? Did you run select st_npoints(the_geom), or ST_GeometryType(the_geom) from it? P On Wed, Sep 19, 2012 at 10:57 AM, Nenad Nikolic nnikoli...@gmail.com wrote: As the subject suggests I need to create Linestrings by connecting Points.I have one table locations that looks like this: CREATE TABLE locations ( userid integer NOT NULL DEFAULT 0, time integer NOT NULL DEFAULT 0, timestamp bigint, the_geom geometry(Point,4326), CONSTRAINT locations_pkey PRIMARY KEY (userid , time ) ) Basically it's a table with a lot of locations with Point geometries. Field 'time' is relative timestamp, from 0 to 179. So for every userid there is 180 entries with 180 locations. What i need to do here is to create a new table which will have one Linestring per userid, which was calculated from Points in locations table, of course in correct order defined in time attribute. So new table should look like this: CREATE TABLE trajectories ( userid int NOT NULL default '0', the_geom geometry(Linestring, 4326), PRIMARY KEY ( userid ) ) I can't find a way to do that. Is it even possible to connect multiple points (in this problem 180 of them) to one Linestring with st_makeline? I tried something like this SELECT St_MakeLine(the_geom) as the_route, bp.userid FROM (SELECT userid, the_geom, time FROM locations ORDER BY time) bp GROUP BY bp.userid ORDER BY bp.userid; But result has geometries field empty... ___ 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 speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
There are few things left to tweak here... your streams are (presumably) relatively small features? your input polygon is very small and simple, so that's not the problem This is one of the few cases where I actually wonder about PostgreSQL tuning parameters: is your shared_buffered increased to a reasonable amount relative to your physical memory (50% of physical memory up to about 3GB)? If you're having to pull those records off physical disk, that could slow things down. Does the query run faster the second time you run it? (see how much caching effects change things) To the extent that your drawn polygon is non-square, chopping it into smaller objects will reduce the number of objects that are fetched-but-not-used. Otherwise, chopping it won't yield any big improvements, since it's already so simple. Sorry, not seeing much to tweak, P. On Mon, Sep 17, 2012 at 5:57 PM, Shira Bezalel sh...@sfei.org wrote: I'm trying to determine if the response time we're seeing on a query is reasonable or if there's anything we can do to speed it up. Looking for advice and/or a reality check. In general, we benefit from dicing our large polygon layers to speed up intersection queries (a big thanks to Paul Ramsey for that pointer), but the catch with this particular query is that it is issued from a web-based GIS application where a user draws a (potentially very large) polygon on-the-fly and then total length calculations are made for intersecting line features within this dynamically drawn polygon. I've even tried dicing dynamically, but it seems to only add more overall time. Here is the SQL for one particular polygon I drew while testing: SELECT s.legcode, s.strahler, sum ( st_length ( st_intersection ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 ) ) ) ) * 0.00062137119AS miles FROM baari_streams s WHERE ST_Intersects ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 )) GROUP BY s.legcode, s.strahler ORDER BY s.legcode, s.strahler; The explain analyze output: http://explain.depesz.com/s/PNZ The line table has 254833 records. It has a spatial index and the optimizer is using it. The index is clustered. And I have run vacuum analyze on it. So it takes about 3.5 seconds to return 27 rows (an aggregation of 28863 rows). Is this about as good as can be expected? Ideally, we'd love to see this return in about 1 second or less. Is that unreasonable? Version info: PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit POSTGIS=1.5.3 GEOS=3.2.2-CAPI-1.6.2 PROJ=Rel. 4.7.1, 23 September 2009 LIBXML=2.7.8 USE_STATS Thanks for any insight you can provide. Shira ___ 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 speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
Well, I still feel that 3.5 seconds is too long for pulling and intersecting 2 features, *assuming* those features are not large (not too many vertices) so if you confirm that the streams are not massive objects I'm out of guesses. I'd be interested to stick your data and query into a profiler and see if there's a spanner in the gears somewhere. P. On Tue, Sep 18, 2012 at 10:01 AM, Shira Bezalel sh...@sfei.org wrote: Hi Paul, Thanks for the response. It's helpful to have the confirmation that this is probably about as fast as we're going to get for now. The shared_buffers parameter is set to 3 GB (total machine RAM = 12 GB) so I think we're good on that front. Yes, it's somewhat faster the second time I run the query. Observing the cache hit ratio, it definitely looks like it is having to pull records off physical disk the first time. But can we do anything about this? Isn't the first read always going to be from disk? And the second time from cache? Is there a way to bring the data into cache ahead of time? Shira From: Paul Ramsey pram...@opengeo.org To: PostGIS Users Discussion postgis-users@postgis.refractions.net Sent: Tuesday, September 18, 2012 5:47:36 AM Subject: Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly? There are few things left to tweak here... your streams are (presumably) relatively small features? your input polygon is very small and simple, so that's not the problem This is one of the few cases where I actually wonder about PostgreSQL tuning parameters: is your shared_buffered increased to a reasonable amount relative to your physical memory (50% of physical memory up to about 3GB)? If you're having to pull those records off physical disk, that could slow things down. Does the query run faster the second time you run it? (see how much caching effects change things) To the extent that your drawn polygon is non-square, chopping it into smaller objects will reduce the number of objects that are fetched-but-not-used. Otherwise, chopping it won't yield any big improvements, since it's already so simple. Sorry, not seeing much to tweak, P. On Mon, Sep 17, 2012 at 5:57 PM, Shira Bezalel sh...@sfei.org wrote: I'm trying to determine if the response time we're seeing on a query is reasonable or if there's anything we can do to speed it up. Looking for advice and/or a reality check. In general, we benefit from dicing our large polygon layers to speed up intersection queries (a big thanks to Paul Ramsey for that pointer), but the catch with this particular query is that it is issued from a web-based GIS application where a user draws a (potentially very large) polygon on-the-fly and then total length calculations are made for intersecting line features within this dynamically drawn polygon. I've even tried dicing dynamically, but it seems to only add more overall time. Here is the SQL for one particular polygon I drew while testing: SELECT s.legcode, s.strahler, sum ( st_length ( st_intersection ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 ) ) ) ) * 0.00062137119AS miles FROM baari_streams s WHERE ST_Intersects ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 )) GROUP BY s.legcode, s.strahler ORDER BY s.legcode, s.strahler; The explain analyze output: http://explain.depesz.com/s/PNZ The line table has 254833 records. It has a spatial index and the optimizer is using it. The index is clustered. And I have run vacuum analyze on it. So it takes about 3.5 seconds to return 27 rows (an aggregation of 28863 rows). Is this about as good as can be expected? Ideally, we'd love to see this return in about 1 second or less. Is that unreasonable? Version info: PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit POSTGIS=1.5.3 GEOS=3.2.2-CAPI-1.6.2 PROJ=Rel. 4.7.1, 23 September 2009 LIBXML=2.7.8 USE_STATS Thanks for any insight you can provide. Shira ___ 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
Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
Please do put it online and let me know. P On Tue, Sep 18, 2012 at 2:49 PM, Shira Bezalel sh...@sfei.org wrote: Well, I don't *think* the lines layer is large, but it's hard for me to say. Here are some stats from the intersecting lines on the original query: Number of lines: 28863 Sum of vertices/points = 296079 Avg number of points per feature = 10.258 Is that considered large or small? I'd be happy to pass on the streams layer if you're interested in taking a look at it. I've got a 40 MB zipped up shape file that I can send to you or place on a FTP site. Shira From: Paul Ramsey pram...@opengeo.org To: PostGIS Users Discussion postgis-users@postgis.refractions.net Sent: Tuesday, September 18, 2012 11:25:41 AM Subject: Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly? Well, I still feel that 3.5 seconds is too long for pulling and intersecting 2 features, *assuming* those features are not large (not too many vertices) so if you confirm that the streams are not massive objects I'm out of guesses. I'd be interested to stick your data and query into a profiler and see if there's a spanner in the gears somewhere. P. On Tue, Sep 18, 2012 at 10:01 AM, Shira Bezalel sh...@sfei.org wrote: Hi Paul, Thanks for the response. It's helpful to have the confirmation that this is probably about as fast as we're going to get for now. The shared_buffers parameter is set to 3 GB (total machine RAM = 12 GB) so I think we're good on that front. Yes, it's somewhat faster the second time I run the query. Observing the cache hit ratio, it definitely looks like it is having to pull records off physical disk the first time. But can we do anything about this? Isn't the first read always going to be from disk? And the second time from cache? Is there a way to bring the data into cache ahead of time? Shira From: Paul Ramsey pram...@opengeo.org To: PostGIS Users Discussion postgis-users@postgis.refractions.net Sent: Tuesday, September 18, 2012 5:47:36 AM Subject: Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly? There are few things left to tweak here... your streams are (presumably) relatively small features? your input polygon is very small and simple, so that's not the problem This is one of the few cases where I actually wonder about PostgreSQL tuning parameters: is your shared_buffered increased to a reasonable amount relative to your physical memory (50% of physical memory up to about 3GB)? If you're having to pull those records off physical disk, that could slow things down. Does the query run faster the second time you run it? (see how much caching effects change things) To the extent that your drawn polygon is non-square, chopping it into smaller objects will reduce the number of objects that are fetched-but-not-used. Otherwise, chopping it won't yield any big improvements, since it's already so simple. Sorry, not seeing much to tweak, P. On Mon, Sep 17, 2012 at 5:57 PM, Shira Bezalel sh...@sfei.org wrote: I'm trying to determine if the response time we're seeing on a query is reasonable or if there's anything we can do to speed it up. Looking for advice and/or a reality check. In general, we benefit from dicing our large polygon layers to speed up intersection queries (a big thanks to Paul Ramsey for that pointer), but the catch with this particular query is that it is issued from a web-based GIS application where a user draws a (potentially very large) polygon on-the-fly and then total length calculations are made for intersecting line features within this dynamically drawn polygon. I've even tried dicing dynamically, but it seems to only add more overall time. Here is the SQL for one particular polygon I drew while testing: SELECT s.legcode, s.strahler, sum ( st_length ( st_intersection ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 ) ) ) ) * 0.00062137119AS miles FROM baari_streams s WHERE ST_Intersects ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 )) GROUP BY s.legcode, s.strahler ORDER BY s.legcode, s.strahler; The explain analyze output: http://explain.depesz.com/s/PNZ The line table has 254833 records. It has a spatial index
Re: [postgis-users] CentOS PostGIS installation problem
Your answer is there, libgeos_c.so.1: cannot open shared object file for some reason your postgis can't find your libgeos. The usual reason is that it got installed in /usr/local/lib, but the linker doesn't know about /usr/local/lib. This can be fixed by adding /usr/local/lib to /etc/ld.so.conf and running /sbin/ldconfig P. On Thu, Sep 13, 2012 at 6:34 PM, Carol Peck carolap...@gmail.com wrote: Hi, I'm trying to get PostGIS running on CentOS 5.8 (on a VPS). I have Postgres 8.4 installed from rpm. I built PostGIS 1.5 from source, install and make went properly. When I try to run postgis.sql I get the following, starting with the first line: psql:/usr/share/pgsql/contrib/postgis-1.5/postgis.sql:59: ERROR: could not load library /usr/lib64/pgsql/postgis-1.5.so: libgeos_c.so.1: cannot open shared object file: No such file or directory and then later after aborted transaction errors: psql:/usr/share/pgsql/contrib/postgis-1.5/postgis.sql:7782: ERROR: type box2d does not exist psql:/usr/share/pgsql/contrib/postgis-1.5/postgis.sql:7783: ERROR: type geometry does not exist psql:/usr/share/pgsql/contrib/postgis-1.5/postgis.sql:7791: ERROR: type geometry[] does not exist Thanks in advance for any help! ___ 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] RAM requirement of shp2psql and psql command for 368 MB data
Both commands should stream the data, not load it into memory, so you should not see any RAM problems at all. P. On Mon, Aug 27, 2012 at 5:27 AM, Tanja Kantola tmkan...@gmail.com wrote: I haven't got PostGIS installed and have never used it. However, I would quickly need to make an estimate of how much RAM space would it require to run shp2psql and psql -f commands on shape dataset, which size is 368 MB. Does any experience user have an estimate of how light/ heavy would these processes be? ___ 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] st_area yields negative value for multi-part polygon
It's not a valid polygon, SELECT st_isvalidreason(ST_GeomFromText('POLYGON((-1 -1, 2 2, 0 1, -1 -1),(3 3, 5 5, 3 5, 3 3))')); st_isvalidreason -- Hole lies outside shell[3 3] (1 row) So the answer is going to be unpredictable. P. On Fri, Aug 17, 2012 at 12:58 PM, george young georgeryo...@gmail.com wrote: PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.4 20100630 (Red Hat 4.4.4-10), 64-bit postgis-2.0.1, libgeos_c.so.1.7.5 gis=# SELECT st_area(ST_GeomFromText('POLYGON((-1 -1, 2 2, 0 1, -1 -1),(3 3, 5 5, 3 5, 3 3))')); st_area - -0.5 what does this mean? What is negative area? In general, what are the semantics of polygons with more than one sequence of vertexes? ___ 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] Endpoint and startpoint
Does this clarify things at all? select st_endpoint('MULTILINESTRING((0 0, 1 1))'); select st_endpoint('LINESTRING(0 0, 1 1)'); P. On Fri, Aug 3, 2012 at 3:38 PM, Bob Pawley rjpaw...@shaw.ca wrote: This is why I enjoy the challenges of Postgis so much. There is always something new to learn. (no sarcasm intended) I have two lines each of which returns an endpoint. However the st_endpoint return on the st_union of those lines returns null. I would appreciate it if someone could shed some light on why this happens and is there a workaround?? Bob ___ 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] ST_Covers for Geography
Poorly worded error message: only calls of the form st_covers(polygon, point) are supported. st_covers(poly, poly) is not P. On Tue, Jul 24, 2012 at 11:53 AM, DrYSG ygutfre...@draper.com wrote: I am getting this error: *geography_covers: only POLYGON and POINT types are currently supported* But I can't see what is wrong. Especially, since I have an ST_Intersects that matches this request, but does not return the error. As far as I can tell, I am passing in Polygons (cat.gpoly) is a geography polygon. ST_Covers version: *SELECT cat.idx,cat.size_bytes,cat.date,cat.type,cat.elevation,cat.source,cat.egpl_date,cat.classification,cat.classification_int,cat.handling,cat.originator,cat.datum,cat.product,cat.description,cat.bbox,cat.path,cat.year FROM portal.catalog AS cat WHERE ST_Covers(st_geogfromtext('SRID=4326;POLYGON((-70.88758389282220 42.50890460863670,-71.24841610717770 42.50890460863670,-71.24841610717770 42.22276957602410,-70.88758389282220 42.22276957602410,-70.88758389282220 42.50890460863670))'), cat.gpoly) AND (cat.date = '1/1/1900 5:00:00 AM' AND cat.date = '7/24/2012 2:45:06 PM') LIMIT 5000;* ST_Intersects version: (which works fine) and has the same values: *SELECT cat.idx,cat.size_bytes,cat.date,cat.type,cat.elevation,cat.source,cat.egpl_date,cat.classification,cat.classification_int,cat.handling,cat.originator,cat.datum,cat.product,cat.description,cat.bbox,cat.path,cat.year FROM portal.catalog AS cat WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((-70.88758389282220 42.50890460863670,-71.24841610717770 42.50890460863670,-71.24841610717770 42.22276957602410,-70.88758389282220 42.22276957602410,-70.88758389282220 42.50890460863670))'), cat.gpoly) AND (cat.date = '1/1/1900 5:00:00 AM' AND cat.date = '7/24/2012 2:45:03 PM') LIMIT 5000;* -- -1 row(s) affected. -- View this message in context: http://postgis.17.n6.nabble.com/ST-Covers-for-Geography-tp4999120.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
Re: [postgis-users] Signal 11: Segmentation fault
Actually, this doesn't match our fixed but, which was an index node-splitting bug that only triggered when new data was inserted. But your crash is happening then you query. The difference between the whole table and the limit query points to the problem being a specific record. The fact that you could dump the table and restore it and *still* get the problem is very surprising. The idea that it could all work for a while and then simply start failing is the most confusing part of all. Can you share (a) a dump of your table (b) the queries you run (c) the exact versions you are running (d) your operating system information (e) your hardware platform If we can replicate the bug, we can probably fix it. You don't have to share your info publicly, just email me and/or strk. P. On Fri, Jul 20, 2012 at 12:49 AM, Rebecca Clarke r.clark...@gmail.com wrote: But that doesn't explain why it's just started happening. Can you give me a link that tells me what the bug is. Cheers On Thu, Jul 19, 2012 at 6:22 PM, Sandro Santilli s...@keybit.net wrote: Sounds like a bad bug recently discovered and already fixed in the repository. You'll have to upgrade to 1.5.5, but it isn't out yet. If you filed a bug it may put some more pressure to the release manager :) --strk; On Thu, Jul 19, 2012 at 05:37:12PM +0100, Rebecca Clarke wrote: Hi there I'm running Postgresql 8.4 with postgis 1.5. I have a table that contains roughly 2000 records. The geometry in the table is a polygon. I can run the following query with no problems select the_geom from mytable; However when I run something like this: select st_centroid(the_geom) from mytable; or select st_isvalid(the_geom) from mytable; I get the following error everytime: 2012-07-19 17:23:47 BST LOG: server process (PID 19445) was terminated by signal 11: Segmentation fault 2012-07-19 17:23:47 BST LOG: terminating any other active server processes 2012-07-19 17:23:47 BST LOG: all server processes terminated; reinitializing 2012-07-19 17:23:47 BST LOG: database system was interrupted; last known up at 2012-07-19 17:23:33 BST 2012-07-19 17:23:47 BST LOG: database system was not properly shut down; automatic recovery in progress 2012-07-19 17:23:47 BST LOG: record with zero length at 1406/B6388B94 2012-07-19 17:23:47 BST LOG: redo is not required 2012-07-19 17:23:47 BST LOG: autovacuum launcher started 2012-07-19 17:23:47 BST LOG: database system is ready to accept connections If I limit the query to 100 records it works. In the same table I have another geometry that is a point. When I run the st_isvalid against that, it does not error. What is strange is this has only just started happening. I thought it was a hardware issue, so I completely dumped the database and recreated it on another server but the issue persisted. I'm not sure what I have done wrong. The data originates from an mdb. The geom's were stored as OLE Objects. When I imported them into postgres they were bytea. I converted them with st_geomfromewkb(the_geom). Any help or directions on where I can go to figure out how to solve this issue would be great. Thanks Rebecca ___ 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] Error compiling postgis 2.0.1 in Centos 6.3
Hard to go wrong with Devrim's RPMs, fire and forget. Your particular problem is probably caused by /usr/local/lib not being in /etc/ld.so.conf (the library exists, but the linker isn't aware of it until you configure that directory in) P. On Wed, Jul 18, 2012 at 12:59 PM, Devrim GÜNDÜZ dev...@gunduz.org wrote: On Wed, 2012-07-18 at 12:48 -0700, Nahum Castro wrote: I have this error when compiling postgis 2.0.1 snip http://yum.postgresql.org/9.1/redhat/rhel-6.3-x86_64/repoview/postgis2_91.html has the RPM if you want, so that you don't need to compile from sources. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz ___ 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] oddity with computing areas
Without seeing your data it's hard to say. Do either of your shapes cross the dateline or poles? P. On Tue, Jul 17, 2012 at 2:12 PM, Eric McKeeth eldi...@gmail.com wrote: Hello. I'm seeing behavior I don't understand when computing the area of the intersection of two geometries. Given the columns geom_a and geom_b, both MULTIPOLYGON geometries with the same SRID (4269 in this case), I'm selecting st_area(st_intersection(geom_a, geom_b)). In most cases, I'm getting results that look correct. But in a small percentage of cases, I'm getting cases where the area is much larger than expected (i.e. as much as 30x greater than the larger of st_area(geom_a) and st_area(geom_b)). It seems to me that it should always hold that the area of the smaller of two geometries should be the upper bound for the area of their intersection. I'm only seeing the issue in cases where the two geometries intersect, but neither contains the other. Does anyone have any ideas as to what could cause this? Thank you in advance for any help, -Eric ___ 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] Why should these boxes intersect?
Your pacific polygon crosses the dateline. Dateline handling is fraught. http://blog.opengeo.org/2010/08/10/shape-of-a-polygon/ For the Geometry type, we just don't do it. We assume a cartesian plane, so your pacific polygon is actually a world-girdling huge polygon that runs from the far west to the far east. For the Geography type, you should find you get the right answer. P. On Wed, Jul 11, 2012 at 8:10 AM, DrYSG ygutfre...@draper.com wrote: Try this in PostGIS 2.0 and tell me why this should return true? SELECT ST_Intersects( st_geomfromtext('SRID=4326;POLYGON((174.375 51.9230769,-168.75 51.9230769,-168.75 41.5384615,174.375 41.5384615,174.375 51.9230769))'), st_geomfromtext('SRID=4326;POLYGON((-70.89651028442381 42.462445702609216,-71.23948971557616 42.462445702609216,-71.23948971557616 42.269406003536325,-70.89651028442381 42.269406003536325, -70.89651028442381 42.462445702609216))') ); I.E I don't think a box in the Boston area should intersect one in the northern pacific. Am I wrong? -- View this message in context: http://postgis.17.n6.nabble.com/Why-should-these-boxes-intersect-tp4998895.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
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
Since others get crashes on 1.5.4, I tested *exactly* that version and lo, it does in fact crash. The current 1.5 stable branch does not crash, so finding the different might not be hard... (also points to needing a 1.5.5) P. On Tue, Jul 10, 2012 at 1:00 AM, René Fournier m...@renefournier.com wrote: Ticket submitted: http://trac.osgeo.org/postgis/ticket/1903 But the crash problem seems not to happen for everyone. At least, the attached SQL file will crash my PostgreSQL 1.5.4 on Mac OS X 10.7.4 every time. But not for Paul. Would some of you be able to test it on your setup, to see if we can spot a pattern or connection? Thanks. On 2012-07-04, at 11:46 PM, Paul Ramsey wrote: This is all well and good, but we need to know which 300 rows to load. If you have a load file, just strip out the first 301 (or whatever) rows, confirm that the file makes things go boom and then create a ticket on the tracker with the dump file attached. http://trac.osgeo.org/ Thanks! P. On Wed, Jul 4, 2012 at 2:01 AM, René Fournier m...@renefournier.com wrote: I've narrowed the point at which a crash will always happen: If I simply create the table *without* the index... CREATE INDEX address_location ON addresses USING GIST (location); ...it won't crash. (Inserts thousands of rows without a problem.) With that index in place, it will always crash after ~300 rows. And here's the log when it does crash: LOG: server process (PID 98414) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-07-04 10:44:15 CEST LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/78E4A50 LOG: record with zero length at 0/7B52580 LOG: redo done at 0/7B52540 LOG: last completed transaction was at log time 2012-07-04 10:44:40.712517+02 LOG: database system is ready to accept connections On 2012-07-03, at 6:31 PM, Mark Cave-Ayland wrote: On 03/07/12 13:20, René Fournier wrote: So, it seems that the table gets full and corrupted to some extent. After my import script inserts ~290 rows, and then postgres crashes... mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 284 (1 row) The connection to the server was lost. Attempting reset: Failed. So, can't insert any more rows... ! delete from addresses where id 50; You are currently not connected to a database. Postgres client/connection is crashed. ! \q Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4) Type help for help. mydb=# delete from addresses where id 50; DELETE 234 Deleting rows works... mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 50 (1 row) The connection to the server was lost. Attempting reset: Failed. SELECT and DELETE work, but I can't insert any new rows, until... ! \q Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4) Type help for help. mydb=# vacuum;vacuum full;vacuum full analyze; VACUUM VACUUM NOTICE: no notnull values, invalid stats VACUUM mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 50 (1 row) INSERT 0 1 mydb=# So, it appears there's some weird corruption going on... Still, not sure what to try next. My PostGIS is via Macports, not sure how to enable the debug mode... Hi René, I think that you need to create a new bug on the PostGIS bug tracker and upload a file that causes the crash on your system, i.e. it can be run using psql -d postgis_db -f crash.sql so that we can try and reproduce what you are seeing. Also as a matter of interest, do you see anything interesting in the PostgreSQL log file at the time of the crash? ATB, Mark. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
Indeed, there has been a small change since 1.5.4 that moves some testing for unknowns *below* the null test (where they should be) avoiding the null pointer reference that is causing the crash. Index: postgis/geography_gist.c === --- postgis/geography_gist.c(revision 9721) +++ postgis/geography_gist.c(revision 10037) @@ -267,17 +267,15 @@ return 0.0; } - if ( gidx_is_unknown(a) gidx_is_unknown(b) ) - { - return 0.0; - } - if ( a == NULL || gidx_is_unknown(a) ) return gidx_volume(b); if ( b == NULL || gidx_is_unknown(b) ) return gidx_volume(a); + if ( gidx_is_unknown(a) gidx_is_unknown(b) ) + return 0.0; + /* Ensure 'a' has the most dimensions. */ gidx_dimensionality_check(a, b); On Tue, Jul 10, 2012 at 9:50 AM, Paul Ramsey pram...@opengeo.org wrote: Since others get crashes on 1.5.4, I tested *exactly* that version and lo, it does in fact crash. The current 1.5 stable branch does not crash, so finding the different might not be hard... (also points to needing a 1.5.5) P. On Tue, Jul 10, 2012 at 1:00 AM, René Fournier m...@renefournier.com wrote: Ticket submitted: http://trac.osgeo.org/postgis/ticket/1903 But the crash problem seems not to happen for everyone. At least, the attached SQL file will crash my PostgreSQL 1.5.4 on Mac OS X 10.7.4 every time. But not for Paul. Would some of you be able to test it on your setup, to see if we can spot a pattern or connection? Thanks. On 2012-07-04, at 11:46 PM, Paul Ramsey wrote: This is all well and good, but we need to know which 300 rows to load. If you have a load file, just strip out the first 301 (or whatever) rows, confirm that the file makes things go boom and then create a ticket on the tracker with the dump file attached. http://trac.osgeo.org/ Thanks! P. On Wed, Jul 4, 2012 at 2:01 AM, René Fournier m...@renefournier.com wrote: I've narrowed the point at which a crash will always happen: If I simply create the table *without* the index... CREATE INDEX address_location ON addresses USING GIST (location); ...it won't crash. (Inserts thousands of rows without a problem.) With that index in place, it will always crash after ~300 rows. And here's the log when it does crash: LOG: server process (PID 98414) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-07-04 10:44:15 CEST LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/78E4A50 LOG: record with zero length at 0/7B52580 LOG: redo done at 0/7B52540 LOG: last completed transaction was at log time 2012-07-04 10:44:40.712517+02 LOG: database system is ready to accept connections On 2012-07-03, at 6:31 PM, Mark Cave-Ayland wrote: On 03/07/12 13:20, René Fournier wrote: So, it seems that the table gets full and corrupted to some extent. After my import script inserts ~290 rows, and then postgres crashes... mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 284 (1 row) The connection to the server was lost. Attempting reset: Failed. So, can't insert any more rows... ! delete from addresses where id 50; You are currently not connected to a database. Postgres client/connection is crashed. ! \q Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4) Type help for help. mydb=# delete from addresses where id 50; DELETE 234 Deleting rows works... mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 50 (1 row) The connection to the server was lost. Attempting reset: Failed. SELECT and DELETE work, but I can't insert any new rows, until... ! \q Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4) Type help for help. mydb=# vacuum;vacuum full;vacuum full analyze; VACUUM VACUUM NOTICE: no notnull values, invalid stats VACUUM mydb=# select count(*) from addresses;INSERT INTO addresses
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
On Tue, Jul 10, 2012 at 12:24 PM, Sandro Santilli s...@keybit.net wrote: Better cut a 1.5.5 soon. Did the NEWS file contain a note about this already ? No, it was a teeny change that had no ticket :) I know, I'm bad. Has one now. P. --strk; On Tue, Jul 10, 2012 at 09:53:56AM -0700, Paul Ramsey wrote: Indeed, there has been a small change since 1.5.4 that moves some testing for unknowns *below* the null test (where they should be) avoiding the null pointer reference that is causing the crash. Index: postgis/geography_gist.c === --- postgis/geography_gist.c(revision 9721) +++ postgis/geography_gist.c(revision 10037) @@ -267,17 +267,15 @@ return 0.0; } - if ( gidx_is_unknown(a) gidx_is_unknown(b) ) - { - return 0.0; - } - if ( a == NULL || gidx_is_unknown(a) ) return gidx_volume(b); if ( b == NULL || gidx_is_unknown(b) ) return gidx_volume(a); + if ( gidx_is_unknown(a) gidx_is_unknown(b) ) + return 0.0; + /* Ensure 'a' has the most dimensions. */ gidx_dimensionality_check(a, b); On Tue, Jul 10, 2012 at 9:50 AM, Paul Ramsey pram...@opengeo.org wrote: Since others get crashes on 1.5.4, I tested *exactly* that version and lo, it does in fact crash. The current 1.5 stable branch does not crash, so finding the different might not be hard... (also points to needing a 1.5.5) P. On Tue, Jul 10, 2012 at 1:00 AM, René Fournier m...@renefournier.com wrote: Ticket submitted: http://trac.osgeo.org/postgis/ticket/1903 But the crash problem seems not to happen for everyone. At least, the attached SQL file will crash my PostgreSQL 1.5.4 on Mac OS X 10.7.4 every time. But not for Paul. Would some of you be able to test it on your setup, to see if we can spot a pattern or connection? Thanks. On 2012-07-04, at 11:46 PM, Paul Ramsey wrote: This is all well and good, but we need to know which 300 rows to load. If you have a load file, just strip out the first 301 (or whatever) rows, confirm that the file makes things go boom and then create a ticket on the tracker with the dump file attached. http://trac.osgeo.org/ Thanks! P. On Wed, Jul 4, 2012 at 2:01 AM, René Fournier m...@renefournier.com wrote: I've narrowed the point at which a crash will always happen: If I simply create the table *without* the index... CREATE INDEX address_location ON addresses USING GIST (location); ...it won't crash. (Inserts thousands of rows without a problem.) With that index in place, it will always crash after ~300 rows. And here's the log when it does crash: LOG: server process (PID 98414) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-07-04 10:44:15 CEST LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/78E4A50 LOG: record with zero length at 0/7B52580 LOG: redo done at 0/7B52540 LOG: last completed transaction was at log time 2012-07-04 10:44:40.712517+02 LOG: database system is ready to accept connections On 2012-07-03, at 6:31 PM, Mark Cave-Ayland wrote: On 03/07/12 13:20, René Fournier wrote: So, it seems that the table gets full and corrupted to some extent. After my import script inserts ~290 rows, and then postgres crashes... mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 284 (1 row) The connection to the server was lost. Attempting reset: Failed. So, can't insert any more rows... ! delete from addresses where id 50; You are currently not connected to a database. Postgres client/connection is crashed. ! \q Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4) Type help for help. mydb=# delete from addresses where id 50; DELETE 234 Deleting rows works... mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
This is all well and good, but we need to know which 300 rows to load. If you have a load file, just strip out the first 301 (or whatever) rows, confirm that the file makes things go boom and then create a ticket on the tracker with the dump file attached. http://trac.osgeo.org/ Thanks! P. On Wed, Jul 4, 2012 at 2:01 AM, René Fournier m...@renefournier.com wrote: I've narrowed the point at which a crash will always happen: If I simply create the table *without* the index... CREATE INDEX address_location ON addresses USING GIST (location); ...it won't crash. (Inserts thousands of rows without a problem.) With that index in place, it will always crash after ~300 rows. And here's the log when it does crash: LOG: server process (PID 98414) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-07-04 10:44:15 CEST LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/78E4A50 LOG: record with zero length at 0/7B52580 LOG: redo done at 0/7B52540 LOG: last completed transaction was at log time 2012-07-04 10:44:40.712517+02 LOG: database system is ready to accept connections On 2012-07-03, at 6:31 PM, Mark Cave-Ayland wrote: On 03/07/12 13:20, René Fournier wrote: So, it seems that the table gets full and corrupted to some extent. After my import script inserts ~290 rows, and then postgres crashes... mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 284 (1 row) The connection to the server was lost. Attempting reset: Failed. So, can't insert any more rows... ! delete from addresses where id 50; You are currently not connected to a database. Postgres client/connection is crashed. ! \q Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4) Type help for help. mydb=# delete from addresses where id 50; DELETE 234 Deleting rows works... mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 50 (1 row) The connection to the server was lost. Attempting reset: Failed. SELECT and DELETE work, but I can't insert any new rows, until... ! \q Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4) Type help for help. mydb=# vacuum;vacuum full;vacuum full analyze; VACUUM VACUUM NOTICE: no notnull values, invalid stats VACUUM mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') ); count --- 50 (1 row) INSERT 0 1 mydb=# So, it appears there's some weird corruption going on... Still, not sure what to try next. My PostGIS is via Macports, not sure how to enable the debug mode... Hi René, I think that you need to create a new bug on the PostGIS bug tracker and upload a file that causes the crash on your system, i.e. it can be run using psql -d postgis_db -f crash.sql so that we can try and reproduce what you are seeing. Also as a matter of interest, do you see anything interesting in the PostgreSQL log file at the time of the crash? ATB, Mark. ___ 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] Super weird problem: ST_GeomFromText('POINT(-114.112534 50.895364)') works, ST_GeomFromText('POINT(-114.228869 51.152249)') fails -- why?
Yes, it's very important to know your version/platform. Also, does this point crash the back-end when inserted into an empty table, or only when inserted after the initial series of points. If the latter, then the problem isn't the particular point, but how the index is dealing with your particular *collection* of points, so it will be useful to know what that collection is. P. On Mon, Jul 2, 2012 at 4:10 AM, René Fournier m...@renefournier.com wrote: If I try to insert a row containing particular coordinate, the query fails and the DB connection is lost. (By comparison, hundreds of inserts of other coordinates work fine.) Here's a straight copy-and-paste comparison from psql: mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0, ST_GeomFromText('POINT(-114.112534 50.895364)') ) RETURNING id; id - 333 (1 row) INSERT 0 1 mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0, ST_GeomFromText('POINT(-114.228869 51.152249)') ) RETURNING id; The connection to the server was lost. Attempting reset: Failed. ! Here's the table definition: CREATE TABLE public.addresses (id serial NOT NULL, account_id int NOT NULL, territory_id int NOT NULL, location GEOGRAPHY(POINT,4326), PRIMARY KEY (id)); CREATE INDEX location ON addresses USING GIST (location); Strange right? FWIW, the queries are being generated programmatically by a script, so the error is not caused by a typo, since hundreds of other inserts work. Also, I've done a little research, two interesting findings: 1. All the multiplied coordinate values (abs(lat)*abs(lng)) of the SUCCESSFUL inserts are LOWER than the coordinates of failed query. 2. If I create the table without the index on location, the failed inserts suddenly work. So it seems the problem lies with the PostGIS updating the Index -- maybe it doesn't like the size of the values of the larger coordinates? Anyway, if you have any ideas of what I can do to fix this, I would love to hear them. Thanks! ...Rene ___ 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] cross-database queries and choice of database configuration
Because the feature he wants, separately backing up spatial and attributes, can be easily achieved inside one database by putting the different parts in different schemas. At the same time, it would be common to expect to run queries that would going the spatial to the attributes, and that would be very inefficient indeed over a cross-database dblink or fdw connection. Just do not store data you want to join together in different databases, full stop. P. On Fri, Jun 29, 2012 at 9:10 PM, shirabez sh...@sfei.org wrote: Following up on this thread from a few months ago, can you give a rationale for this perspective? Why is it better to go with one database? Performance? Simplicity? Both? Thanks, Shira Paul Ramsey-4 wrote On Thu, Mar 22, 2012 at 4:53 AM, Tim Pigden lt;tim.pigden@gt; wrote: Is this a practical approach for postgis type queries or do I have to put it all in one database to get good querying flexibility? No, bad approach. Put it all in one database, with the application data in one schema and the geo data in another. Then for regular backups you can just backup the application schema separately. P. ___ postgis-users mailing list postgis-users@.refractions http://postgis.refractions.net/mailman/listinfo/postgis-users -- View this message in context: http://postgis.17.n6.nabble.com/cross-database-queries-and-choice-of-database-configuration-tp4644336p4998684.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] PostGIS 2.0.1 Released
The latest PostGIS release is a bug fix release, including a large number of minor issues reported since the release of 2.0.0. http://postgis.org/download/postgis-2.0.1.tar.gz Use and enjoy! * Bug Fixes * - #1264, fix st_dwithin(geog, geog, 0). - #1468 shp2pgsql-gui table column schema get shifted - #1694, fix building with clang. (vince) - #1708, improve restore of pre-PostGIS 2.0 backups. - #1714, more robust handling of high topology tolerance. - #1755, ST_GeographyFromText support for higher dimensions. - #1759, loading transformed shapefiles in raster enabled db. - #1761, handling of subdatasets in NetCDF, HDF4 and HDF5 in raster2pgsql. - #1763, topology.toTopoGeom use with custom search_path. - #1766, don't let ST_RemEdge* destroy peripheral TopoGeometry objects. - #1774, Clearer error on setting an edge geometry to an invalid one. - #1775, ST_ChangeEdgeGeom collision detection with 2-vertex target. - #1776, fix ST_SymDifference(empty, geom) to return geom. - #1779, install SQL comment files. - #1782, fix spatial reference string handling in raster. - #1789, fix false edge-node crossing report in ValidateTopology. - #1790, fix toTopoGeom handling of duplicated primitives. - #1791, fix ST_Azimuth with very close but distinct points. - #1797, fix (ValidateTopology(xxx)).* syntax calls. - #1805, put back the 900913 SRID entry. - #1813, Only show readable relations in metadata tables. - #1819, fix floating point issues with ST_World2RasterCoord and ST_Raster2WorldCoord variants. - #1820 compilation on 9.2beta1. - #1822, topology load on PostgreSQL 9.2beta1. - #1825, fix prepared geometry cache lookup - #1829, fix uninitialized read in GeoJSON parser - #1834, revise postgis extension to only backup user specified spatial_ref_sys - #1839, handling of subdatasets in GeoTIFF in raster2pgsql. - #1840, fix logic of when to compute # of tiles in raster2pgsql. - #1851, fix spatial_ref_system parameters for EPSG:3844 - #1857, fix failure to detect endpoint mismatch in ST_AddEdge*Face* - #1865, data loss in postgis_restore.pl when data rows have leading dashes. - #1867, catch invalid topology name passed to topogeo_add* - #1870, align the docs and actual behavior of raster's ST_Intersects - #1872, fix ST_ApproxSummarystats to prevent division by zero - #1873, fix ptarray_locate_point to return interpolated Z/M values for on-the-line case - #1875, ST_SummaryStats returns NULL for all parameters except count when count is zero * Enhancements * - More detailed exception message from topology editing functions. - #1786, improved build dependencies - #1806, speedup of ST_BuildArea, ST_MakeValid and ST_GetFaceGeometry. - #1812, Add lwgeom_normalize in LIBLWGEOM for more stable testing. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Closed linestring is not closed?
It returns true for me on OSX64. This is probably another Win64 quirk. I wonder why that platform has so many more oddities than win32. P. On Sat, Jun 16, 2012 at 12:11 AM, georgew gws...@hotmail.com wrote: The following linestring which I believed to be closed, and looks closed, in fact gives me false in reply to st_isclosed(geom). It's probably something very simple which is eluding me but I am at a loss why I get this result. I am using postgis 2.0, Win 7 64. Any suggestions?. Thank you select ST_GeomFromEWKT ('SRID=2193;LINESTRING(1581855.1284829 537,1581884.80445 5369966.19158,1581887.30288 5369961.19296,1581902.69023 5369908.80888,1581907.78739 5369901.31078,1581945.46961 5369869.11694,1582053.42388 5369814.82165,1582068.01672 5369800.32473,1582058.81757 5369782.83187,1582041.22099 5369762.24115,1582016.32738 5369744.55038,1582008.82968 5369741.95223,1581956.34859 5369744.1582,1581886.27473 5369753.66394,1581876.27826 5369753.56525,1581858.78411 5369750.96838,1581848.78695 5369745.8714,1581826.49107 5369718.28368,1581829.1853 5369683.29527,1581789.4928 5369635.51667,1581784.59315 5369625.42075,1581782.09266 5369615.42448,1581782.19125 5369605.42788,1581762.49236 5369562.74497,1581757.49345 5369557.74732,1581727.60161 5369540.0572,1581717.6048 5369537.45937,1581690.11486 5369539.76211,1581654.93006 5369559.55987,1581644.93496 5369569.45777,1581639.8378 5369576.95587,1581612.24962 5369591.75436,1581597.25527 5369594.15546,1581584.66075 5369601.55455,1581569.56816 5369616.4514,1581567.06973 5369621.45002,1581564.37413 5369646.44183,1581581.67146 5369671.63101,1581614.16168 5369684.32251,1581626.55902 5369696.91662,1581628.95886 5369701.9146,1581628.95989 5369709.41204,1581626.36184 5369716.90982,1581603.67538 5369756.79911,1581598.57889 5369769.19553,1581595.8826 5369789.18905,1581558.00391 5369846.47434,1581532.81696 5369876.36736,1581527.72047 5369888.76378,1581522.42785 5369928.75081,1581494.84072 5369951.1467,1581492.24266 5369958.5445,1581492.04687 5369988.63425,1581488.15835779 537,1581855.1284829 537)') -- View this message in context: http://postgis.17.n6.nabble.com/Closed-linestring-is-not-closed-tp4998311.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
Re: [postgis-users] loading only geometries of shp files into PostGIS
There's no reason technically you can't do it. You will need to hack the loader to ignore the fact that the other files are missing, but shp file alone does contain all the information you need. P. On Wed, Jun 6, 2012 at 10:23 PM, karsten kars...@terragis.net wrote: ** Hi All, I am working on a data upload function where for a web map a user can upload a shape file onto the server, the file will then be loaded via a utility into PostGIS e.g. using ogr2ogr or shp2pgsql. Now we all know that usually a shape files has at least 3 files shp, shx and dbf to be complete... but unusually - well I was wondering if it would be possible to only use a shp file alone to load the geometry to PostGIS (if it is ok to lose index and attributes) The reason is this would have several advantages for the work I am tying to do 1. the shape file is user provided (if will be always 'simple' polygon shapes, but due to that will always have non consistent attributes (i.e not the same tabel schema) which are not needed anyway for the purposes of the app 2. the geometry alone will be used to show areas of Interest for the user on the map (attributes are not needed, only location and extent matters) 3. inconsistencies of differing attributes will not be a problem if I could in fact only import the geometry to PostGIS and simply add a unique gid and user id as only default attributes to the PostGIS table Any ideas on that weird idea , if that could be accomplished with existing tools without too much trouble ? Cheers Karsten Karsten Vennemann Principal Terra GIS LTD 2119 Boyer Ave E Seattle, WA 98112 USA www.terragis.net** Phone ++1 206 905 1711 Fax ++1 925 905 1711 === Email scanned by PC Tools - No viruses or spyware found. (Email Guard: 9.0.0.888, Virus/Spyware Database: 6.19830) http://www.pctools.comhttp://www.pctools.com/?cclick=EmailFooterClean_51 === ___ 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] ST_Intersects not finding everythng?
Individually your observation does not hold. Testing the literals pairwise returns true in all cases. select st_intersects('SRID=4326;POLYGON((-71.0004167 43.0004167,-69.9995833 43.0004167,-69.9995833 41.9995833,-71.0004167 41.9995833,-71.0004167 43.0004167))', 'srid=4326;POLYGON((-70.70785443115230 42.61915358272220,-71.42814556884760 42.61915358272220,-71.42814556884760 42.11182215470750,-70.70785443115230 42.11182215470750,-70.70785443115230 42.61915358272220))'); You'll have to make a data table available that shows this behavior for others to help. Best, Paul On Thu, Jun 7, 2012 at 12:35 PM, DrYSG ygutfre...@draper.com wrote: I am sure this is my faulty understanding. I have been noticing that some EPSG4326 polygons are only being found only in a smaller search bbox, not in a larger search bbox. Even though the larger box is entirely bigger on all sides (I actually get the bbox from Google Maps). I am using ST_INTERSECTS. There are many others missing also (all items are rectangular bboxes) , here are two: SRID=4326;POLYGON((-72.0004167 43.0004167,-70.9995833 43.0004167,-70.9995833 41.9995833,-72.0004167 41.9995833,-72.0004167 43.0004167)) SRID=4326;POLYGON((-71.0004167 43.0004167,-69.9995833 43.0004167,-69.9995833 41.9995833,-71.0004167 41.9995833,-71.0004167 43.0004167)) Here is the smaller query that finds them: SELECT * FROM portal.catalog cat WHERE ST_Intersects(st_geomfromtext('POLYGON((-70.88792721557620 42.49270487579740,-71.24807278442380 42.49270487579740,-71.24807278442380 42.23903905725250,-70.88792721557620 42.23903905725250,-70.88792721557620 42.49270487579740))'::text, 4326), cat.poly) LIMIT 3800; (this query returns 447 matches) and here is the outset larger bbox search that somehow does not find these contained polygons (but it does find many more new ones: SELECT * FROM portal.catalog cat WHERE ST_Intersects(st_geomfromtext('POLYGON((-70.70785443115230 42.61915358272220,-71.42814556884760 42.61915358272220,-71.42814556884760 42.11182215470750,-70.70785443115230 42.11182215470750,-70.70785443115230 42.61915358272220))'::text, 4326), cat.poly) LIMIT 3800; This one finds 1545 records (FYI cat.poly is a *geometry*, which always has a rectangular *polygon * in it. (Therefore I do NOT suspect the LIMIT statement, since I am not near the limit) I have also tried a similar SQL query, and it returns the same record counts but has the same issue: String sql = String.Format(SELECT * FROM portal.catalog cat WHERE st_geomfromtext('{0}'::text, 4326) cat.poly LIMIT 3800;, poly); -- View this message in context: http://postgis.17.n6.nabble.com/ST-Intersects-not-finding-everythng-tp4998091.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
Re: [postgis-users] more effective way?
You can run ddl in a transaction context, so the best way is to . Upload data to mynewtable . Begin . Alter table mytable rename to myoldtable . Alter table mynewtable rename to mytable . Commit . Drop table myoldtable P. On 2012-06-03, at 7:02 AM, chris brisendine ki4...@gmail.com wrote: I have a postgis table that contains radar. My problem is every 3 minutes I update the radar table and sometimes the end user has no radar showing due to the new data loading is there a more effective way than doing it like I am with the following code, shp2pgsql -D -a -s 4269 last.shp base05 base05.sql psql -c TRUNCATE TABLE base05 -d wxserver psql -d wxserver -f base05.sql I was dropping the table but that would cause my wms server to freakout. ___ 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] Can't figure out query to find lat/lon (numeric types) in time zone (geometry type). New to GIS.
select tz.tzid from tz_world tz, locations locs where st_contains(tz.the_geom, st_makepoint(locs.longitude, locs.latitude)) and loc.id = ?; On Thu, May 31, 2012 at 12:57 PM, Scott Chapman sc...@mischko.com wrote: I have the following table for time zone data: CREATE TABLE tz_world ( gid serial NOT NULL, tzid character varying(30), the_geom geometry, CONSTRAINT tz_world_pkey PRIMARY KEY (gid ), CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (-1)) ) and a location table: CREATE TABLE locations ( id bigserial NOT NULL, latitude numeric, longitude numeric, ) Given a location id, I want to know what time zone it's in. I'm brand new to GIS and have not been able to figure out this query. I have PostgreSQL 9.1 and PostGIS 1.5.3 installed and set up, and the timezone data imported using the shape file import wizard. Can someone please give me an example of how to query lat/lon in the_geom? Thanks! Scott ___ 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] Speeding up point-in-polygon search using ST_SimplifyPreserveTopology?
Evan, Beyond the cutting-up-of-large-things as Stephen suggests, there is the extra development work to optimize the edge searching routines with an internal index on the objects (and caching that index once built). Something for 2.1 :) P. On Mon, May 28, 2012 at 10:08 AM, Evan Martin postgre...@realityexists.net wrote: Hi all, I have a table of ~350 multi-polygons and ~185,000 points and I need to find which points are inside which multi-polygons. Some polygons are quite large and span the dateline, so I'm using geography ST_DWithin for this (with a tolerance of 100m). My initial query looks like this (simplified): SELECT ... FROM points, polygons WHERE ST_DWithin(point, real_area, 100) This works, but it takes about 90 minutes. I'm trying to make it faster by using ST_SimplifyPreserveTopology. That worked very nicely for my adjacent polygons problem [http://postgis.refractions.net/pipermail/postgis-users/2012-January/031992.html], because all polygons were modified in the same way, but this is trickier. Since I'm modifying the polygon, but not the point, the results are different. So I thought maybe I could do this in two phases: if the point is well within or well outside the polygon then take the result of the simplified check as correct, but if it's close to the border then check it properly, ie. SELECT ... FROM points, polygons WHERE ST_DWithin(point, simple_area, 2) AND (ST_Distance(point, simple_border) 2 OR ST_DWithin(point, real_area, 100)) simple_area = ST_SimplifyPreserveTopology(real_area::geometry, 0.01)::geography and simple_border = the exterior ring of simple_area. This takes about 18 minutes (a 5x improvement) and gives very similar results, but not quite the same. It falls down on polygons that have rhumblines along parallels, because they get turned into great circle lines. Eg. the original polyon may have a rhumbline approximated as (24 10,25 10,26 10,27 10), ST_SimplifyPreserveTopology does its job and simplifies it to (24 10,27 10) and then ST_DWithin on geography treats it as a great circle line, giving an incorrect result. I tried inserting extra points to unsimplify the rhumblines, but that itself is very slow and also quite a hack, because I can't really be sure which lines were supposed be rhumblines when looking at the simplified polygon. I feel like I'm so close and this is a very silly little problem - but it is a problem. Could anyone suggest a way to work around this? Or a different approach to the whole thing? Thanks, Evan ___ 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] Speeding up point-in-polygon search using ST_SimplifyPreserveTopology?
On Wed, May 30, 2012 at 9:47 AM, Evan Martin postgre...@realityexists.net wrote: Thanks, Paul, but do you have any suggestions on how I can cut up the polygons properly? As I posted, ST_Intersection doesn't work properly on the ones spanning the dateline and I don't know how else to do it. Maybe there's some obvious trick I'm missing. (I haven't looked at the code, but it seems a bit strange to me that ST_Intersects works, but not ST_Intersection - so PostGIS can somehow figure out that they intersect, but can't figure out where?) Intersects() is run on the spheroid, Intersection is a hack wrapper on a reprojection into planar and then back again. Did we talk about this already? Changing the hack to use gnomic instead of UTM might improve things. By the development work do you mean ticket 1796 or something else? Either way, any estimate on when it might get done? :) Yes, that's the ticket. It might get done for 2.1, or it might not. Money makes the world go 'round. All things being equal it will get done, since it's something that personally interests me, but things might not be equal if I'm paid for some other work instead. P. Regards, Evan On 30/05/2012 11:35 PM, Paul Ramsey wrote: Evan, Beyond the cutting-up-of-large-things as Stephen suggests, there is the extra development work to optimize the edge searching routines with an internal index on the objects (and caching that index once built). Something for 2.1 :) P. On Mon, May 28, 2012 at 10:08 AM, Evan Martin postgre...@realityexists.net wrote: Hi all, I have a table of ~350 multi-polygons and ~185,000 points and I need to find which points are inside which multi-polygons. Some polygons are quite large and span the dateline, so I'm using geography ST_DWithin for this (with a tolerance of 100m). My initial query looks like this (simplified): SELECT ... FROM points, polygons WHERE ST_DWithin(point, real_area, 100) This works, but it takes about 90 minutes. I'm trying to make it faster by using ST_SimplifyPreserveTopology. That worked very nicely for my adjacent polygons problem [http://postgis.refractions.net/pipermail/postgis-users/2012-January/031992.html], because all polygons were modified in the same way, but this is trickier. Since I'm modifying the polygon, but not the point, the results are different. So I thought maybe I could do this in two phases: if the point is well within or well outside the polygon then take the result of the simplified check as correct, but if it's close to the border then check it properly, ie. SELECT ... FROM points, polygons WHERE ST_DWithin(point, simple_area, 2) AND (ST_Distance(point, simple_border) 2 OR ST_DWithin(point, real_area, 100)) simple_area = ST_SimplifyPreserveTopology(real_area::geometry, 0.01)::geography and simple_border = the exterior ring of simple_area. This takes about 18 minutes (a 5x improvement) and gives very similar results, but not quite the same. It falls down on polygons that have rhumblines along parallels, because they get turned into great circle lines. Eg. the original polyon may have a rhumbline approximated as (24 10,25 10,26 10,27 10), ST_SimplifyPreserveTopology does its job and simplifies it to (24 10,27 10) and then ST_DWithin on geography treats it as a great circle line, giving an incorrect result. I tried inserting extra points to unsimplify the rhumblines, but that itself is very slow and also quite a hack, because I can't really be sure which lines were supposed be rhumblines when looking at the simplified polygon. I feel like I'm so close and this is a very silly little problem - but it is a problem. Could anyone suggest a way to work around this? Or a different approach to the whole thing? Thanks, Evan ___ 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] Nearest neighbor in 3D
Using ST_3DDistance will work and give you the right answer, but it will not be index assisted. The KNN operator - is only implemented against the 2D index currently. Paul On Fri, May 25, 2012 at 5:15 PM, Bartosz Bekier bartosz.bek...@gmail.com wrote: Hello All! This is my first post on the PostGIS mailing list:-). I would like to use the - to solve the nearest neighbor problem, but in 3D space. I tried to do this in 2D and it works perfectly, but apparently it doesn't cover the 3D case. Basically I have a table of 3D points (x, y, z) and would like to do: ORDER BY point - POINT(0 0 0) but the distance taken into account should be 3D Euclidean distance. Do I have to use ST_3DDistance() for it or is there a faster way? Thanks in advance! Regards, -- Bartosz Bekier ___ 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] Nearest neighbor in 3D
Using ST_3DDistance will work and give you the right answer, but it will not be index assisted. The KNN operator - is only implemented against the 2D index currently. Paul On Fri, May 25, 2012 at 5:15 PM, Bartosz Bekier bartosz.bek...@gmail.com wrote: Hello All! This is my first post on the PostGIS mailing list:-). I would like to use the - to solve the nearest neighbor problem, but in 3D space. I tried to do this in 2D and it works perfectly, but apparently it doesn't cover the 3D case. Basically I have a table of 3D points (x, y, z) and would like to do: ORDER BY point - POINT(0 0 0) but the distance taken into account should be 3D Euclidean distance. Do I have to use ST_3DDistance() for it or is there a faster way? Thanks in advance! Regards, -- Bartosz Bekier ___ 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] AddToPROJ4SRSCache: couldn't parse proj4 string - major axis or radius = 0 or not given
I think the proj parser might dislike the line breaks you've embedded in your row. Try to get rid of them. Also if you've set your +ellps and +datum you don't need an +a parameter. P. On Wed, May 23, 2012 at 7:00 PM, David Wu d...@delcan.com wrote: Hi there, I need to add a projection to support an imported shapefiles dataset with the following projection: PROJCS [ Business Analyst Projection, GEOGCS[ GCS_WGS_1984, DATUM[ D_WGS_1984,SPHEROID[WGS_1984,6378137.0,298.257223563]], PRIMEM[Greenwich,0.0], UNIT[Degree,0.0174532925199433]], PROJECTION[Equidistant_Conic], PARAMETER[False_Easting,0.0], PARAMETER[False_Northing,0.0], PARAMETER[Central_Meridian,-88.02657853618898], PARAMETER[Standard_Parallel_1,41.82305904132364], PARAMETER[Standard_Parallel_2,41.82305804132364], PARAMETER[Latitude_Of_Origin,0.0], UNIT[Meter,1.0] ] I used the following SQL to add the projection to the spatial_ref_sys table: INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) VALUES (9998, 'EPSG', 9998, 'PROJCS[Business Analyst Projection, GEOGCS[GCS_WGS_1984, DATUM[D_WGS_1984,SPHEROID[WGS_1984,6378137.0,298.257223563]], PRIMEM[Greenwich,0.0], UNIT[Degree,0.0174532925199433]], PROJECTION[Equidistant_Conic], PARAMETER[False_Easting,0.0], PARAMETER[False_Northing,0.0], PARAMETER[Central_Meridian,-88.02657853618898], PARAMETER[Standard_Parallel_1,41.82305904132364], PARAMETER[Standard_Parallel_2,41.82305804132364], PARAMETER[Latitude_Of_Origin,0.0], UNIT[Meter,1.0]]', '+proj=eqdc +lat_0=41.82305904132364 +lon_0=-88.02657853618898 +lon_1=-88.02657853618898 +lon_2=-88.02657853618898 +x_0=0 +y_0=0 +a=6378137.0 +ellps=WGS84 +datum=WGS84 +units=m +no_defs'); When I tried to do a transform test [ update major set geom=transform(geom, 4326) ], I go the following error: ERROR: AddToPROJ4SRSCache: couldn't parse proj4 string: '+proj=eqdc +lat_0=41.82305904132364 +lon_0=-88.02657853618898 +lon_1=-88.02657853618898 +lon_2=-88.02657853618898 +x_0=0 +y_0=0 +a=6378137.0 +ellps=WGS84 +datum=WGS84 +units=m +no_defs': major axis or radius = 0 or not given BTW, I found no documentation that provides a complete listing of parameters I need to set for eqdc projection. Thanks for any hint. DWW P Please consider the environment before printing this email. This communication may contain information that is confidential, privileged or subject to copyright. If you are not the intended recipient, please advise by return e-mail and delete the message and any attachments immediately without reading, copying or forwarding to others. ___ 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] Linear referencing with PostGIS
Muni, PostGIS certainly has LRS capabilities (see http://postgis.org/documentation/manual-1.3/ch06.html#id2578698). Whether they meet your needs depends on the structure of your data and the questions you are asking of it. The use case you provide (given a line and a query point, read the measure) is supported. I'm sure you have many others. P. On Wed, May 16, 2012 at 10:59 PM, Melpati, Muni muni.melp...@dot.state.fl.us wrote: We have various web-based GIS applications that consume the data from enterprise database (oracle/ArcSDE) . These applications need linear referencing capabilities of the ArcSDE. We currently do this using java API provided by ESRI. Some of these web application provide user capabilities to query distance, in miles, of a road segment give a location on the road. Our data has linear referencing capabilities and other features such as topology which are essential to our business needs. I came to know while older PostGIS doesn’t provide such capabilities. I am wondering if newer versions offer any similar functionalities. Is it possible to provide similar capabilities with PostGIS and Postgresql? Thanks in advance. ___ 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] z,m,zm geometries
On Tue, May 15, 2012 at 3:02 PM, Duarte Carreira dncarre...@gmail.com wrote: Hi there. I'm trying to convert from esri's st_ geometry type to postgis' one. I can't convert to wkt using esri's functions and build a geometry in postgis using geomfromtext. Problems start when there are z, m, or zm coordinates. Mind that I'm on postgis 1.4 since that's the latest supported by esri. Now, when I convert from esri using their function I get a text like: LINESTRING Z ... When I pass it to geomfromtext, or even st_geomfromewkt, postgis does not like this... I posted on esri's forum and got a slap in the hand saying postgis is not following the ogc standard... I've done some reading and all I could find are references to POINTZ, POLYGONZ, etc. (suffixes withouth spaces) in v.1.2.0 of the standard. But this does not seem to be the spec for encoding wkt, just the geometry type to be put into geometry_columns. Either way, I could not get postgis 1.4 to accept pointz or any other geometryz type. I found references to postgis 2.0 doing this... but that is beyong my grasp for now. So my 2 questions are: - is there anyway i can get this to work in postgis 1.4 (main question)? Are you wedded to the Z? Do you have a way to strip the Z from the ESRI geometry before outputting the text? Perhaps you could try WKB instead? (Still might have problems with type numbers, but maybe worth a try?) - and as long as we're in the subject, how is this related to the ogc sfs spec? are they right? The 1.0 and 1.1 specs made no mention of higher dimensions (and hence provided no guidance on what form the WKT should take). Not sure if 1.2 did, but I don't think so. ISO SQL/MM does talk about them, and that's the pattern ESRI is following. PostGIS 2.0 consumes that kind of text, but that's no solace to you. P. Thanks for your time! Regards, Duarte ___ 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] z,m,zm geometries
On Tue, May 15, 2012 at 3:02 PM, Duarte Carreira dncarre...@gmail.com wrote: - is there anyway i can get this to work in postgis 1.4 (main question)? Oh, you're getting text and PostgreSQL has some quite powerful regex functions. You can probably strip the Z from the text before passing it along to PostGIS. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] segmentation fault
My bet: You've compiled and installed postgis2.0 on top of a postgis1.5 database. That is, you haven't re-loaded your data, you're trying to use your old data with the new code. That would probably cause segfaults on simple things like object access. P. On Thu, May 10, 2012 at 10:06 PM, Gold, Jack L (US SSA) jack.g...@baesystems.com wrote: It appears it is the postgis-2.0.so library that is causing the problem. I removed all but one call the library and it still occurred. I tried some basic queries with no problem. I’ll have to go back to my compilation. --Jack Gold From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Gold, Jack L (US SSA) Sent: Thursday, May 10, 2012 4:53 PM To: PostGIS Users Discussion (postgis-users@postgis.refractions.net) Subject: [postgis-users] segmentation fault I’m trying to rebuild my i386(RHEL5) database (PostGIS-2.0.1SVN) on x86_64 (CentOS). I’ve managed to build all of the libraries and I believe they are all installed correctly, but when I attempt to load postgis.sql, I get a seg-fault and the postmaster restarts wth: …LANGUAGE 'plpgsql' IMMUTABLE STRICT; ERROR: current transaction is aborted, commands ignored until end of transaction block STATEMENT: CREATE OR REPLACE FUNCTION _ST_AsX3D(int4, geometry, int4, int4, text) RETURNS TEXT AS '$libdir/postgis-2.0','LWGEOM_asX3D' LANGUAGE 'C' IMMUTABLE; ERROR: current transaction is aborted, commands ignored until end of transaction block STATEMENT: CREATE OR REPLACE FUNCTION ST_AsX3D(geom geometry, maxdecimaldigits integer DEFAULT 15, options integer DEFAULT 0) RETURNS TEXT AS $$SELECT _ST_AsX3D(3,$1,$2,$3,'');$$ LANGUAGE 'sql' IMMUTABLE; LOG: received fast shutdown request LOG: aborting any active transactions LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2012-05-10 20:42:21 UTC LOG: autovacuum launcher started LOG: database system is ready to accept connections LOG: server process (PID 8543) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-05-10 20:42:22 UTC LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/2A9A0F0 LOG: redo is not required LOG: autovacuum launcher started LOG: database system is ready to accept connections LOG: server process (PID 8572) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-05-10 20:42:28 UTC LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/2A9A148 LOG: redo is not required LOG: autovacuum launcher started LOG: database system is ready to accept connections LOG: server process (PID 8581) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2012-05-10 20:42:33 UTC LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/2A9A1A0 LOG: redo is not required LOG: autovacuum launcher started LOG: database system is ready to accept connections I’m looking around for more detail, but I’m not seeing anything. Any ideas where I should look next?
Re: [postgis-users] ST_Within and GEOMETRYCOLLECTION
On Mon, May 7, 2012 at 4:41 PM, Martin Davis mtncl...@telus.net wrote: It's because the underlying JTS/GEOS operation doesn't support GeometryCollections. And this in turn is because of the details of the implementation - in particular, handling the situation where two polygons overlap. It would require computing the overlay of the polygons in order to detect the within condition at the point of overlap. Why so? If you're in one component, does that end the test? The answer is 'true'. P. On 5/7/2012 7:52 AM, Hemin Tofiq wrote: Hi, The documentation about ST_Within says: Do not call with a GEOMETRYCOLLECTION as an argument, without saying why. I can not finde more Info about taht, du anyone has an explanation? http://postgis.refractions.net/docs/ST_Within.html Thanks in advance, Hemin -- View this message in context: http://postgis.17.n6.nabble.com/ST-Within-and-GEOMETRYCOLLECTION-tp4957949.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 - No virus found in this message. Checked by AVG - www.avg.com Version: 2012.0.1913 / Virus Database: 2425/4983 - Release Date: 05/07/12 ___ 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] Storing image file metadata in PostGIS
PostGIS has little or nothing to say about reading image metadata, but you'll find that you can get a long way by wrapping a bit of scripting around the output of 'gdalinfo' http://gdal.orgl or just using the gdal python bindings directly to read the metadata. Once you have extracted footprints, resolutions, and so on using GDAL, I'd suggest storing them as polygons in a 'geography' column in PostGIS. That will provide nice global indexing, no problems with datelines or poles, and a simple query model. P. On Sat, May 5, 2012 at 9:31 PM, Billy Newman newman...@gmail.com wrote: Hello all, I am currently using a pain old Oracle (not Oracle Spatial) database to store metadata for a huge amount of imagery data. I store this metadata for file discovery purposes. I.E. when a request is made for imagery I can quickly hit the DB to get all overlapping image files. I can then grab those off the file system and serve them out. I am using imageio-ext (Java) that sits on top of gdal to pull the imagery's metadata. Then just sending that to the DB. I am probably doing this the hard way and wondering if PostGIS can help simplify things. I am wondering if PostGIS has support to store metadata for images, and what that support really gives me. Currently I am serving out images in only EPSG:4326 so I want to do any transformations (if the image is not in EPSG:4326) to the metadata before storing it in the database. Can PostGIS read an image file and extract the metadata, transform the metadata into an EPSG:4326 lat/lon bounding box and store it? This is my current data model in Oracle if that helps: DataSource (this contains general info about this group of image files) ImageFile ( each data source has 0 - N image files) double minLat double maxLat double minLon double maxLon Its as simple as that. Although I am doing quite a bit of work using imageio-ext, and gdal to try and extract, transform, and store the metadata. Is this less complex in PostGIS or essentially the same? Thanks! Billy ___ 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.4 Released
Download from: http://postgis.org/download/postgis-1.5.4.tar.gz The news: This is a bug fix release, addressing issues that have been filed since the 1.5.3 release. - Bug Fixes - #547, ST_Contains memory problems (Sandro Santilli) - #621, Problem finding intersections with geography (Paul Ramsey) - #627, PostGIS/PostgreSQL process die on invalid geometry (Paul Ramsey) - #810, Increase accuracy of area calculation (Paul Ramsey) - #852, improve spatial predicates robustness (Sandro Santilli, Nicklas Avén) - #877, ST_Estimated_Extent returns NULL on empty tables (Sandro Santilli) - #1028, ST_AsSVG kills whole postgres server when fails (Paul Ramsey) - #1056, Fix boxes of arcs and circle stroking code (Paul Ramsey) - #1135, improve testsuite predictability (Andreas 'ads' Scherbaum) - #1146, images generator crashes (bronaugh) - #1170, North Pole intersection fails (Paul Ramsey) - #1179, ST_AsText crash with bad value (kjurka) - #1184, honour DESTDIR in documentation Makefile (Bryce L Nordgren) - #1227, server crash on invalid GML - #1252, SRID appearing in WKT (Paul Ramsey) - #1264, st_dwithin(g, g, 0) doesn't work (Paul Ramsey) - #1344, allow exporting tables with invalid geometries (Sandro Santilli) - #1389, wrong proj4text for SRID 31300 and 31370 (Paul Ramsey) - #1406, shp2pgsql crashes when loading into geography (Sandro Santilli) - #1595, fixed SRID redundancy in ST_Line_SubString (Sandro Santilli) - #1596, check SRID in UpdateGeometrySRID (Mike Toews, Sandro Santilli) - #1602, fix ST_Polygonize to retain Z (Sandro Santilli) - #1697, fix crash with EMPTY entries in GiST index (Paul Ramsey) - #1772, fix ST_Line_Locate_Point with collapsed input (Sandro Santilli) - #1799, Protect ST_Segmentize from max_length=0 (Sandro Santilli) - Alter parameter order in 900913 (Paul Ramsey) - Support builds with gmake (Greg Troxel) ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Looking inside a blob
You're necessarily going to have to unzip to view things. However, with the blob API you should be able to get access to a file handle, and with the right code could do things like view a manifest or extract individual files without pulling the whole blob over the wire to a client. P. On Wed, Apr 25, 2012 at 9:40 AM, White, Richard richard.wh...@infoterra-global.com wrote: Hi, Not entirely postgis related. Is it possible to view the contents of zipped directories stored as blobs in a postgresql database without having to extract and unzip the data? Thanks. Infoterra Ltd. Is part of the Astrium GEO-Information Services Division and a wholly owned subsidiary of Astrium, Europe's leading space systems and services specialist. Disclaimer. The information contained in this e-mail and its attachments are confidential and intended only for the use of the named addressee(s). If you are not the intended addressee, please do not read, copy, use or disclose this message or its attachments. If you have received this message in error, please notify the sender immediately and delete or destroy all copies of this message and attachments in all media. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Infoterra Ltd and shall not form part of any binding agreement. Infoterra Limited a company registered in England under number 2359955 and having its registered office at Atlas House, 41 Wembley Road, Leicester, LE3 1UT. VAT number GB 476 0468 27. P Before printing, think about the environment ___ 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] crash on ST_Clip()
Find the exact geometry pair that is causing the crash and file a ticket. (Yes, disconnecting means the backend went boom, that's a crash). P On Wed, Apr 25, 2012 at 12:15 PM, Stephen Crawford src...@psu.edu wrote: All, I have a query which works on my desktop Windows (postgis2.0 RC) but crashes on Linux (postgis2.0 official release). And by crash i mean disconnects me from the server. INSERT INTO result_raster_table (observation_date, rast) SELECT r.observation_date, ST_Clip(r.rast,g.the_geom,-) AS rast FROM raster_table r, geom_table g WHERE r.observation_date = '2012-03-09'; 'raster_table ' holds tiles, 187 of which make up one observation_date, and 'geom_table' is just one record, the union of ~23 Eastern US states. Any thoughts? Thanks, Steve -- Stephen Crawford Center for Environmental Informatics The Pennsylvania State University src...@psu.edu 814.865.9905 ___ 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] crash on ST_Clip()
In order to fix a problem, developers must reproduce it. What do we need to reproduce it: the offending SQL and the data itself. In ideal test cases the users can embed the data statically into a single SQL statement. In others they provide minimal versions of the offending tables. Try to make reproducing the problem as easy as possible for others, and the odds of it being fixed go up. Create a ticket with the SQL and description and attach your data to it. http://trac.osgeo.org/postgis P. On Wed, Apr 25, 2012 at 1:29 PM, Stephen Crawford src...@psu.edu wrote: Well I tried the same query for just one of the tiles that I know intersects the the_geom but it crashed. Then i simplified it further by using just a box instead of a multipolygon and it still crashed. On 4/25/2012 3:22 PM, Paul Ramsey wrote: Find the exact geometry pair that is causing the crash and file a ticket. (Yes, disconnecting means the backend went boom, that's a crash). P On Wed, Apr 25, 2012 at 12:15 PM, Stephen Crawfordsrc...@psu.edu wrote: All, I have a query which works on my desktop Windows (postgis2.0 RC) but crashes on Linux (postgis2.0 official release). And by crash i mean disconnects me from the server. INSERT INTO result_raster_table (observation_date, rast) SELECT r.observation_date, ST_Clip(r.rast,g.the_geom,-) AS rast FROM raster_table r, geom_table g WHERE r.observation_date = '2012-03-09'; 'raster_table ' holds tiles, 187 of which make up one observation_date, and 'geom_table' is just one record, the union of ~23 Eastern US states. Any thoughts? Thanks, Steve -- Stephen Crawford Center for Environmental Informatics The Pennsylvania State University src...@psu.edu 814.865.9905 ___ 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 -- Stephen Crawford Center for Environmental Informatics The Pennsylvania State University src...@psu.edu 814.865.9905 ___ 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] Use of tolerances in topology
Your understanding is correct, the approach is mostly brute force, and I have a pile of code lying about waiting to go in that implements what you describe in terms of building internal indexes. P. On Tue, Apr 17, 2012 at 9:29 AM, Jose Carlos Martinez jomar...@cgf.upv.es wrote: Thanks Sandro, I understand now. About trying to enhance ST_Dwithin, last time I checked PostGIS code it was implemented using brute force (after comparing the boxes). Maybe one way to improve the algorithm (DT_Dwithin) will be to use spatial indexes inside a geometry. According to my tests (long time ago with JTS) I remember it could be faster to build a spatial index every time for a geometry and use it than using brute force (with geometries with more than 20 vertexes or so). Anyways Im not sure if PostGIS is still using brute force with st_dwithin, if so then forget everything I said. Regards, On 17/04/2012 17:46, Sandro Santilli wrote: On Tue, Apr 17, 2012 at 05:35:42PM +0200, Jose Carlos Martinez wrote: Hi Sandro, Im learning from topology.sql how the topology functions are working with more detail. I have a question about how the tolerances are managed, for example: I see (ST_AddIsoEdge) that you have used some times spatial predicates as st_contains or st_intersects. As you know these predicates (and others too) just work in a good way if the geometries are snapped to each other previously, so my question is why you didnt use st_Dwithin with a tolerance? or maybe these functions expect the geometries to be already snapped by totopogeom for example and they shouldnt be used directly? Those functions are ISO functions and as such are not specified to deal with a tolerance. Handling of tolerance is not always straightforward as using ST_DWithin. It may also have a big cost. So take all those functions as being expected to be already snapped. I'm open to suggestion about enhanced versions taking tolerance into consideration. But it must be possible to invoke such functions in a fast way, when caller already did their checking and noding and what not. The idea is that all the ST_ prefixed functions end up being just wrappers around more flexible functions in core. --strk; ,--o-. | __/ | Delivering high quality PostGIS 2.0 ! | / 2.0 | http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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] st_astext and st_asewkt with z and m
On Fri, Apr 13, 2012 at 2:56 PM, Jose Carlos Martinez Llario jomar...@cgf.upv.es wrote: Hi list, st_astext is using Z, ZM as a suffix. st_asewkt is not. Is this the expected behaviour? Yes, it is. P. select st_astext(geom), st_asewkt(geom) from (select 'POINT (0 0 0)'::geometry) as tabla(geom); st_astext | st_asewkt -+-- POINT Z (0 0 0) | POINT(0 0 0) Thanks, Jose ___ 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] Invalid SRID for geography type: ee4
You can't load projected coordinates into geography, you'll need to load to geometry, then re-project to 4326 and cast to geography if you really want to use geography (why do you want to if your data is all neatly contained in Belgium and already projected?) P. On Tue, Apr 10, 2012 at 4:50 PM, Philippe watche...@gmail.com wrote: Hello, I'm running into an issue loading data from the Belgium IGN. I can transform a shapefile into Postgis loadable data with Geometry but not Geography. I can't imagine why. Any ideas ? Thanks The following works /usr/lib/postgresql/9.1/bin/shp2pgsql -WLATIN1 -s3812 -d -I /home/postgres/warehouse/shapes/BE/vector/Shape_L08/commune_polygon.shp be_ign /tmp/be_ign.sql The following fails /usr/lib/postgresql/9.1/bin/shp2pgsql -WLATIN1 -s3812 -G -d -I /home/postgres/warehouse/shapes/BE/vector/Shape_L08/commune_polygon.shp be_ign /tmp/be_ign.sql with Shapefile type: Polygon Postgis type: MULTIPOLYGON[2] Invalid SRID for geography type: ee4 Although 0xee4=3812 which is the EPSG for the Lambert 2008 projection used by that file. And my spatial ref table contains it: public=# select * from spatial_ref_sys where auth_srid=3812; srid | 3812 auth_name | EPSG auth_srid | 3812 srtext | PROJCS[ETRS89 / Belgian Lambert 2008,GEOGCS[ETRS89,DATUM[European_Terrestrial_Reference_System_1989,SPHEROID[GRS 1980,6378137,298.257222101,AUTHORITY[EPSG,7019]],AUTHORITY[EPSG,6258]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4258]],UNIT[metre,1,AUTHORITY[EPSG,9001]],PROJECTION[Lambert_Conformal_Conic_2SP],PARAMETER[standard_parallel_1,49.84],PARAMETER[standard_parallel_2,51.16],PARAMETER[latitude_of_origin,50.797815],PARAMETER[central_meridian,4.3592158],PARAMETER[false_easting,649328],PARAMETER[false_northing,665262],AUTHORITY[EPSG,3812],AXIS[X,EAST],AXIS[Y,NORTH]] proj4text | +proj=lcc +lat_1=49.84 +lat_2=51.16 +lat_0=50.797815 +lon_0=4.3592158 +x_0=649328 +y_0=665262 +ellps=GRS80 +units=m +no_defs ___ 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: Geometry has Z dimension but column does not
On Thu, Apr 5, 2012 at 11:15 AM, Sandro Santilli s...@keybit.net wrote: On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote: My apologies, I sent the previous post in a hurry. Below is the query I am using to insert a geography from text. I still receive the error indicated. I am, in fact, looking to insert a geography with a Z value. CREATE TABLE myTable(location geography(PointZ,4326)); INSERT INTO myTable (location) VALUES (st_geographyFromText('SRID=4326;PointZ(85 35 500)')); ERROR: Geometry has Z dimension but column does not Works for me. What's the result of : SELECT Postgis_full_version() ? It does? Doesn't for me! working this now. P --strk; ,--o-. | __/ | Delivering high quality PostGIS 2.0 ! | / 2.0 | http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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: Geometry has Z dimension but column does not
Working here. It's a problem with the st_geographyFromText somehow, you can insert values into the table if you feed it text directly. INSERT INTO myTable (location) VALUES ('SRID=4326;PointZ(85 35 500)'); P On Thu, Apr 5, 2012 at 11:16 AM, Paul Ramsey pram...@opengeo.org wrote: On Thu, Apr 5, 2012 at 11:15 AM, Sandro Santilli s...@keybit.net wrote: On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote: My apologies, I sent the previous post in a hurry. Below is the query I am using to insert a geography from text. I still receive the error indicated. I am, in fact, looking to insert a geography with a Z value. CREATE TABLE myTable(location geography(PointZ,4326)); INSERT INTO myTable (location) VALUES (st_geographyFromText('SRID=4326;PointZ(85 35 500)')); ERROR: Geometry has Z dimension but column does not Works for me. What's the result of : SELECT Postgis_full_version() ? It does? Doesn't for me! working this now. P --strk; ,--o-. | __/ | Delivering high quality PostGIS 2.0 ! | / 2.0 | http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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: Geometry has Z dimension but column does not
Fixed. http://trac.osgeo.org/postgis/ticket/1755 On Thu, Apr 5, 2012 at 11:23 AM, Salvatore Larosa lrssv...@gmail.com wrote: Il giorno gio, 05/04/2012 alle 11.16 -0700, Paul Ramsey ha scritto: On Thu, Apr 5, 2012 at 11:15 AM, Sandro Santilli s...@keybit.net wrote: On Thu, Apr 05, 2012 at 01:53:59PM -0400, Gold, Jack L (US SSA) wrote: My apologies, I sent the previous post in a hurry. Below is the query I am using to insert a geography from text. I still receive the error indicated. I am, in fact, looking to insert a geography with a Z value. CREATE TABLE myTable(location geography(PointZ,4326)); INSERT INTO myTable (location) VALUES (st_geographyFromText('SRID=4326;PointZ(85 35 500)')); ERROR: Geometry has Z dimension but column does not Works for me. What's the result of : SELECT Postgis_full_version() ? It does? Doesn't for me! working this now. P Same problem here. I get: ERROR: Geometry has Z dimension but column does not -SL ___ 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] XSD2PostGIS
Not from XSD that I know of, but OGR GML reader might be able to import a GML file for you. P. On Wed, Apr 4, 2012 at 1:47 PM, Piotr Pachół piotrpac...@gmail.com wrote: Hello, Is there any way to import spatial table structure (attributes and geometry) from XSD file to PostGIS ? Geometry in XSD file is based on gml ver. 3.2 Piotr ___ 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 2.0.0 Released
The PostGIS development team is super excited, can hardly believe that they are actually doing this, aren't maybe even sure that they are ready to make this kind of commitment, not so young, and not when we have so much more living to do, but: PostGIS 2.0.0 is complete and available for download. http://postgis.org/download/postgis-2.0.0.tar.gz The development process for 2.0 has been very long, but has resulted in a release with a number of exciting new features. * Raster data and raster/vector analysis in the database * Topological models to handle objects with shared boundaries * PostgreSQL typmod integration, for an automagical geometry_columns table * 3D and 4D indexing * Index-based high performance nearest-neighbour searching * Many more vector functions including * ST_Split * ST_Node * ST_MakeValid * ST_OffsetCurve * ST_ConcaveHull * ST_AsX3D * ST_GeomFromGeoJSON * ST_3DDistance * Integration with the PostgreSQL 9.1 extension system * Improved commandline shapefile loader/dumper * Multi-file import support in the shapefile GUI * Multi-table export support in the shapefile GUI * A geo-coder optimized for free US Census TIGER (2010) data We are greatly indebted to our large community of beta testers who valiantly tested PostGIS 2.0.0 and reported bugs so we could squash them before release time. And also we want to thank our parents for making PostGIS possible. Yours, The PostGIS development team ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostGIS 2.0.0rc2
The second release candidate of PostGIS 2.0.0 is now available: http://postgis.org/download/postgis-2.0.0rc2.tar.gz We hope you'll test and let us know (http://trac.osgeo.org/postgis) if you find problems! Yours, The PostGIS Team ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Z coordinate value changes with st_transform
Turns out this is just because the definition of 23030 has been updated to include a to_wgs84 parameter. You get the same behavior in any version of proj. This is a feature, not a bug (our projection information is now better). If you want the old behaviour, change the proj4text of 23030 back to the 1.5 version. For a future release you might file a ticket to allow users to apply transforms x/y but to skip the z-coordinate. P. On Fri, Mar 30, 2012 at 10:27 AM, Paul Ramsey pram...@opengeo.org wrote: Yeah, bad news, 2.0 is showing this behavour but not 1.5. http://trac.osgeo.org/postgis/ticket/1742 P. On Fri, Mar 30, 2012 at 9:59 AM, Stefano Iacovella stefano.iacove...@gmail.com wrote: Hi Jose Im sure which version of PostGis are you using. On my box, Postgis 1.5.3 with Proj 4.8 it seems to work without any change to elevation: template_postgis=# select st_asewkt(st_transform(st_geomfromtext('POINT (70 450 100)', 23030), 4326)); WARNING: OGC WKT expected, EWKT provided - use GeomFromEWKT() for this st_asewkt -- SRID=4326;POINT(-0.635451186670006 40.6259397158234 100) (1 row) template_postgis=# select postgis_full_version(); postgis_full_version -- POSTGIS=1.5.3 GEOS=3.3.2-CAPI-1.7.2 PROJ=Rel. 4.8.0, 6 March 2012 LIBXML= 2.7.6 USE_STATS (1 row) Regards Stefano 2012/3/30 Jose Carlos Martinez jomar...@cgf.upv.es: I didnt get nay answers from now. Someone can tell me why postgis is changing the z coordinate and how i can change this default behavior? Should I keep using proj4.7 instead of proj4.8? Before: pg20=# select st_astext(st_transform(st_geomfromtext('POINT (70 450 100)', 23030), 4326)); st_astext -- POINT Z (-0.63662073736203 40.6248036513963 100) Now: pg20=# select st_astext(st_transform(st_geomfromtext('POINT (70 450 100)', 23030), 4326)); st_astext -- POINT Z (-0.63662073736203 40.6248036513963 168.35361468256) On 27/03/2012 21:56, Jose Carlos Martinez Llario wrote: From proj4.8 changelog: Preliminary support has been added for vertical datums using the +geoidgrids= directive. The geoidgrids are assumed to provide a transformation from geoid elevations to ellipsoidal elevations and they are discussed a bit in the VerticalDatums topic on the web site I dont like the st_transform method can change my z coordinates by default though, dont know if it is because PostGIS is not ready still for proj4.8 or because it is the proj4.8 default behaviour. cheers, Jose On 27/03/2012 19:02, Jose Carlos Martinez wrote: Hi, dont know if this is the expected behavior, maybe its because I built postgis with proj 4.8 or because something has changed in spatial_ref_sys. Dont know at what time it happened (I missed it). Before: pg20=# select st_astext(st_transform(st_geomfromtext('POINT (70 450 100)', 23030), 4326)); st_astext -- POINT Z (-0.63662073736203 40.6248036513963 100) Now: pg20=# select st_astext(st_transform(st_geomfromtext('POINT (70 450 100)', 23030), 4326)); st_astext -- POINT Z (-0.63662073736203 40.6248036513963 168.35361468256) Using ST_AsKml Its changing the Z coordinate too. Someone can confirm when this change happened in PostGIS? Regards, Jose ___ 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] LinearReferencing: measure vertex of linestring?
Create a ticket describing the calibration, that's useful info and would be a good utility for LRS users for a future version. P. 2012/3/28 Andrés Maneiro amane...@icarto.es: Hello people, I'm reviewing what postgis is able to do in terms of Linear Referencing. As fas as I could see, to calibrate a linestring there is only the method ST_AddMeasure: http://www.postgis.org/docs/ST_AddMeasure.html So, as stated in the example, the operation will create a route like: SELECT ST_AsEWKT(ST_AddMeasure( ST_GeomFromEWKT('LINESTRING(1 0, 2 0, 4 0)'),1,4)) As ewelev; ewelev LINESTRINGM(1 0 1,2 0 2,4 0 4) What I'm wondering is if somehow I could calibrate the vertex of the linestring, having as a result, for example, this route: LINESTRINGM(1 0 1,2 0 *1.7*,4 0 4) The project I'm working on is related to road management, and it's very common that the ratio between a route's length and its measures is not constant. So, my doubt goes: is it somehow possible to create a route in postgis where the measure and the distance are not directly related? (For those who know how ArcGIS dynamic segmentation works, what I'd like to achive is something similar to calibrate a route using points [1]). Let me know if I explain myself properly. best, Andrés [1] http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/About_calibrating_route_measures_using_points/0039000t00/ ___ 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 2.0.0rc1
The first release candidate of PostGIS 2.0.0 is now available: http://postgis.org/download/postgis-2.0.0rc1.tar.gz We hope you'll test and let us know (http://trac.osgeo.org/postgis) if you find problems! Yours, The PostGIS Team ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Slony and PostGIS problem
On Fri, Mar 23, 2012 at 6:18 AM, Brian Peschel bri...@occinc.com wrote: Figured I would start here since I suspect this is a PostGIS problem more than a Slon problem. My main node is a Fedora 8, Postgres 8.3.1, Slon 1.2.20, and PostGIS 1.3. This is being replicated via slon to an Ubuntu 10, Postgres 8.4.11, Slon 1.2.20, and PostGIS 1.4. My table looks like this: Table public.ml_polygon Column | Type | Modifiers +--+--- msg_no | integer | not null rev_no | integer | not null geom | geometry | Indexes: ml_polygon_pkey PRIMARY KEY, btree (msg_no, rev_no) ml_geom_idx gist (geom) Check constraints: enforce_dims_geom CHECK (ndims(geom) = 2) enforce_srid_geom CHECK (srid(geom) = 4269) I have some bad geoms in the database, basically polygons with invalid point counts: 70650590 | 1 | POLYGON((-90.869358 38.839444,-90.869358 38.839447,-90.869358 38.839444)) In PostGIS 1.3, this is perfectly acceptable (even though it is bad). In 1.4, this doesn't work and a select gives you this error: ERROR: geometry requires more points which also appears in my Postgres log. Google tells me this was an issue with early PostGIS 1.4 versions. Okay. But I need to fix these. So on my main node, I did this: update ml_polygon set geom = ST_SetSRID('POLYGON((-90.869358 38.839444,-90.869358 38.839447,-90.869358 38.839447,-90.869358 38.839444))', 4269) where msg_no = 70650590 and rev_no = 1; which happily updated my main node. And nothing showed up on by second node. No errors messages anywhere I could find. New inserts in this table in my main node showed up on the second node, so I know slony is working, but this update never showed up. I did a different update (to an existing 16 point polygon) and the updated showed up in my second node. I was worried that slony wouldn't pick up updates of a geometry column, but this proved to me that wasn't the problem. I re-did the first insert and, surprise, surprise, it worked fine. So, I tried a different record with the same 3 point problem, and it showed up on the second node. Needless to say, I am very confused. Not only is the constancy of what works and fails a problem, but the fact the slony copy (apparently) failed with no error messages concerned me also. Can I trust the data in my second node? Based on the description I would think not. If you insert another new bad geometry in the master, does it show up on the slave? If the bad geometries can't traverse from 1.3 to 1.4 you'll have a systemic chunk of missing data. Any thoughts? - Brian ___ 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] ST_Mapalgebraexpr requires same alignment
On Fri, Mar 23, 2012 at 8:16 AM, Jose Carlos Martinez jomar...@cgf.upv.es wrote: ummm...there is some plans about future releases to work with not alignment rasters? to be honest I think this is a limitation in order to to carry out a normal raster spatial analysis. :-(. I will try to use postgis raster without this feature though. I don't think it's unreasonable to expect that inputs to mapalgebras be on the same alignment. I do thing that utilities for re-sampling whole tables need to exist to that it's possible to run full analyses inside the database, but I think requiring alignment for mapalgebra will solve more problems (in terms of people getting the results they expect) than trying to plaster over it with automagical code. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] cross-database queries and choice of database configuration
On Thu, Mar 22, 2012 at 4:53 AM, Tim Pigden tim.pig...@optrak.com wrote: Is this a practical approach for postgis type queries or do I have to put it all in one database to get good querying flexibility? No, bad approach. Put it all in one database, with the application data in one schema and the geo data in another. Then for regular backups you can just backup the application schema separately. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] St_intersection
Co-linearity. On Mon, Mar 19, 2012 at 10:44 AM, Bob Pawley rjpaw...@shaw.ca wrote: Hi I am querying a geometry looking for intersection point between a line and an other geometry. The return is 2 points and 2 linestrings. (when I use st_union a geometry collection results) Can anyone tell me under what conditions that a st_intersection would return a linestring?? insert into num_search (the_geom3) select st_intersection(Num_Search.the_geom1, entities.wkb_geometry) from entities, Num_Search where Num_Search.seq = 'R' and st_intersects(entities.wkb_geometry, Num_Search.the_geom1) = True Bob ___ 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] Removed double quotes from column and table names
Check out the pg_* system tables table, you should be able to bulk update the appropriate table to have lower case column names only... P On Mon, Mar 19, 2012 at 3:58 AM, Simon Greener si...@spatialdbadvisor.com wrote: Birgit, I realise that I can do this manually. I was looking for a method for doing this at the point of loading the shapefile into PostGIS using QGIS. I will have a look at ogr2ogr to see if that helps. Thanks regards Simon On Mon, 19 Mar 2012 21:00:27 +1100, Birgit Laggner birgit.lagg...@vti.bund.de wrote: Hi Simon, I would think RENAME would do what you want: ALTER TABLE schema.TableName RENAME TO tablename; ALTER TABLE schema.tablename RENAME COLUMN ColumnName TO columnname; Hope that helps, Birgit. Am 19.03.2012 06:03, schrieb Simon Greener: Is there any way to remove the double quotes created around a table or its column names? My names do NOT include spaces but do include mixed case. I wish to remove the quotes and lower case all names. How can this be done? Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file the names are mixed case. I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to tell it to lower case all names. S ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Holder of 2011 Oracle Spatial Excellence Award for Education and Research. SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18 E) Latitude: -43.01530 (43° 00' 55 S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ 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] PostGIS2beta4svn won't configure / build
Add /usr/local/bin to your path (if that's where you installed it) or specify your gdal-config explicitly. P On Fri, Mar 16, 2012 at 8:10 AM, White, Richard richard.wh...@infoterra-global.com wrote: Hi, I am trying to build PostGIS2beta4svn on Ubuntu Server 11.10 64bit and I get the following error message when trying to configure; Checking for library containing GDALAllRegister...no configure:error: could not find gdal Gdal is installed and built correctly. Any ideas? Thanks. R. Infoterra Ltd. Is part of the Astrium GEO-Information Services Division and a wholly owned subsidiary of Astrium, Europe's leading space systems and services specialist. Disclaimer. The information contained in this e-mail and its attachments are confidential and intended only for the use of the named addressee(s). If you are not the intended addressee, please do not read, copy, use or disclose this message or its attachments. If you have received this message in error, please notify the sender immediately and delete or destroy all copies of this message and attachments in all media. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Infoterra Ltd and shall not form part of any binding agreement. Infoterra Limited a company registered in England under number 2359955 and having its registered office at Atlas House, 41 Wembley Road, Leicester, LE3 1UT. VAT number GB 476 0468 27. P Before printing, think about the environment ___ 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] FOSS4G North America is Almost Here: April 10-11
FOSS4G North America is one month away! Due to venue restrictions (400 max!), we only have a limited number of registration spaces still remaining, so sign up soon! * The full program is here, online at http://foss4g-na.org/program/ * Online registration is here, at http://foss4g-na.org/registration/ * Rooms at nearby hotels are available too, in a limited room block, http://foss4g-na.org/hotel-block-annouced/ We are proud of our program, which includes talks from open source developers, managers and visionaries. FOSS4G North America is a great opportunity to learn about the statue of the art, and how people are putting open source to use in their organizations. We hope to see you in DC! FOSS4G North America Paul Ramsey (Chair), OpenGeo Bonnie Bogle (Program Chair), MapBox ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 2.0.0beta3
It's patched in trunk and the 6.0 release, but there is no numbered release with the fix yet. P On Thu, Mar 15, 2012 at 12:22 PM, Andy Colson a...@squeakycode.net wrote: On 3/14/2012 4:46 PM, Paul Ramsey wrote: On Wed, Mar 14, 2012 at 2:41 PM, Andy Colsona...@squeakycode.net wrote: ... and GeomFromText became ST_GeomFromText ... Why did some drop the ST_ and some have it added? The external spatial functions got them added, so all match up to the standards base. The internal functions, used for casts got them stripped to match PostgreSQL convention: newtype(oldtype) is the PostgreSQL standard casting function signature. There are some other even more internal functions (type_in(), type_out()) that you don't see or use that also had their st_ prefixes stripped. They really should never have had them added in the first place. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users I have run into a problem. Mapserver 6 (.0.1 and .0.2) uses GeomFromText. Are there any mapserver people on this list? I'll post to the map server list otherwise. -Andy ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostGIS 2.0.0beta3
Hi All, PostGIS 2.0.0beta3: http://postgis.org/download/postgis-2.0.0beta3.tar.gz Current open tickets: http://trac.osgeo.org/postgis/report/22 You'll note that the number of open tickets is getting close to zero. Like a rocket ship countdown, when we get to zero, we launch. But we need your help now, more than ever. Please try out PostGIS 2.0. Download it, build it, make sure it works for you. The most important tests now are migrations: can you migrate to PostGIS 2.0? If no, why? Let us know. Thanks!!! ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 2.0.0beta3
On Wed, Mar 14, 2012 at 2:41 PM, Andy Colson a...@squeakycode.net wrote: ... and GeomFromText became ST_GeomFromText ... Why did some drop the ST_ and some have it added? The external spatial functions got them added, so all match up to the standards base. The internal functions, used for casts got them stripped to match PostgreSQL convention: newtype(oldtype) is the PostgreSQL standard casting function signature. There are some other even more internal functions (type_in(), type_out()) that you don't see or use that also had their st_ prefixes stripped. They really should never have had them added in the first place. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] 3D geometries
The main special consideration is that if you don't also densify the lines to the resolution of the dem can can end up with a line that hops valleys and tunnels through ridges. Also, I don't know if we have a draping function. Was something I thought would be useful when I started futzing with the raster stuff, I guess because I have a thing for DEMs. P. On Tue, Mar 13, 2012 at 10:42 AM, Stephen V. Mather s...@clevelandmetroparks.com wrote: Hi All, Any special considerations for turning a 2D multi-line geometry into a 3D geometry using a DEM as a RASTER within PostGIS? Has anyone played in this arena? ** ** Thanks, Steve ** ** **[image: http://www.clemetparks.com/images/esig/cmp-ms-90x122.png]**Stephen Mather Geographic Information Systems (GIS) Manager (216) 635-3243 s...@clevelandmetroparks.com clevelandmetroparks.com http://www.clemetparks.com/ ** ** ** ** ** ** ** ** ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users image001.png___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] update a geometry field from real fields from postgis datatable
you need a space between the ordinates and you need to put the longitude first. P. On Tue, Mar 13, 2012 at 1:52 PM, francis francis.mil...@free.fr wrote: hello, I have a table with two columns '*lat*' and '*lon*' : it is real type double and a column '*geom*' type geometry; how to make a request to update my column geom from 'lat' and 'lon' columns ? somewhat I wrote this : *update mytable set geom = 'POINT (' | | lat | | '' | | lon | | ')';* but it does not work! Do you have an idea? thank you my config Windows XP Pro SP2 PostGIS 1.5.3 -- -- ___ 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] Using ST_intersection on Polygons returns multiple types
ST_Intersection is perhaps a bit more complete than most users would like. If two polygons share a boundary what is their intersection? Not a polygon, but that linear, shared boundary: a line. Similar for touching at a point. Anyhow, probably a cleaner approach would be an extra optional argument to ST_Intersection allowing users to specify the dimensionally of the return, but in the meanwhile you can go to ST_CollectionExtract and use that to pull out just the polygons. P. On Wed, Mar 7, 2012 at 9:58 AM, Rebecca Clarke rebe...@clarke.net.nz wrote: Hi there I have 2000 polygons in table A and I want to show only the polygons that intersect with the bounding box specified in table B . So I do this: SELECT the_geom FROM A WHERE st_intersects(the_geom, (select the_geom from B)) AND ST_GeometryType(the_geom) = 'ST_Polygon' This query returns all the intersecting polygons. When I run st_geometrytype they are all St_Polygon. Some of the polygons are large and exceed the boxes boundary so I use this query to trim them: SELECT st_intersection(the_geom, (select the_geom from B)) as the_geom FROM A WHERE st_intersects(the_geom, (select the_geom from B)) AND ST_GeometryType(the_geom) = 'ST_Polygon' The problem I'm having is that when I use the st_intersection, instead of returning just St_Polygon's, it's returning other types as well: ST_LineString ST_MultiLineString ST_MultiPolygon ST_Polygon I need to output the results to a SHP file and this is preventing me from doing so seeing there is more than one Geom Type. Any ideas how I can keep the results of the st_intersection as just St_Polygons? Am I doing something wrong? Regards Rebecca ___ 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 2.0.0beta2
Hi All, PostGIS 2.0.0beta2: http://postgis.org/download/postgis-2.0.0beta2.tar.gz Current open tickets: http://trac.osgeo.org/postgis/report/22 You'll note that the number of open tickets is getting close to zero. Like a rocket ship countdown, when we get to zero, we launch. But we need your help now, more than ever. Please try out PostGIS 2.0. Download it, build it, move your databases to it, make sure it works for you. Thanks!!! ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Slow delete of duplicate rows
This will be the fastest approach since it involves a new table creation instead of re-writing an existing table. Good call Stephen. P. On Mon, Mar 5, 2012 at 8:17 AM, Stephen Woodbridge wood...@swoodbridge.com wrote: On 3/5/2012 11:12 AM, DrYSG wrote: I have a large table (20M records) but mostly short text fields. There are duplicates that I am trying to remove. I have a bigseriel index that I unique, but duplicates in another field. I have an 8 core, 24GB memory computer with RAID disks. This request has been running for 70 hours (is it safe to kill it?). Yes, it is safe. How can I make this run faster? This is a one time processing task, but it is taking a long time. CREATE TABLE portal.new_metatdata AS select distinct on (data_object.unique_id) * FROM portal.metadata; Or something of this ilk should be faster because it only need to do a sort on data_object.unique_id and then an insert. After you have verified the results you can do: BEGIN; ALTER TABLE portal.metatdata rename TO portal.new_metatdata_old; ALTER TABLE portal.new_metatdata rename TO portal.metatdata_old; COMMIT; Other people might have other ideas. -Steve DELETE FROM portal.metadata WHERE idx NOT IN ( SELECT MIN(idx) FROM portal.metadata GROUP BY data_object.unique_id ); CREATE TABLE metadata ( data_object.unique_id CHARACTER(64) NOT NULL, size_bytes BIGINT, object_date TIMESTAMP(6) WITHOUT TIME ZONE, object_type CHARACTER VARYING(25), classification CHARACTER VARYING(7), object_source CHARACTER VARYING(50), object_managed_date TIMESTAMP(6) WITHOUT TIME ZONE, clevel INTEGER, fsctlh CHARACTER VARYING(50), oname CHARACTER VARYING(40), description CHARACTER VARYING(80), horizontal_datum CHARACTER VARYING(20), do_location CHARACTER VARYING(200), elevation_ft INTEGER, location USER-DEFINED, idx BIGINT DEFAULT nextval('portal.metadata_idx_seq'::regclass) NOT NULL, bbox CHARACTER VARYING(160), CONSTRAINT MetaDataKey PRIMARY KEY (idx) ) -- View this message in context: http://postgis.17.n6.nabble.com/Slow-delete-of-duplicate-rows-tp4548251p4548251.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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to create a hole in a polygon table?
Note that I don't know the correct syntax, but functionally, I would expect this to work... Difference(geom, Buffer(geom, -something)) P. On Tue, Feb 28, 2012 at 10:06 PM, Robert Buckley robertdbuck...@yahoo.com wrote: Thanks for that...shame it didn´t work. I have already looked at negative buffers, but they create a polygon within a polygon and not a hole within a negative-buffered polyon. i.e my polygon needs to be transparent in the center past the negative buffer so underlying features can be seen. Andrea Aime thought it could be done with the Difference SLD filter, but I can´t seem to get it to work due to my lack of understanding of how the filter works. http://docs.geoserver.org/latest/en/user/styling/sld-reference/filters.html yours, Rob Von: Paul Ramsey pram...@opengeo.org An: Robert Buckley robertdbuck...@yahoo.com; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 22:33 Dienstag, 28.Februar 2012 Betreff: Re: [postgis-users] how to create a hole in a polygon table? I was thinking of something like this, but it doesn't work. Unfortunately Geoserver doesn't have the ability to create offset lines, so there's no nice way to do this without doing a little negative buffer on the shape (which incidentally, you could do using the geometry transformation facility on the fly http://docs.geoserver.org/stable/en/user/styling/sld-extensions/geometry-transformations.html P ?xml version=1.0 encoding=ISO-8859-1? StyledLayerDescriptor version=1.0.0 xmlns=http://www.opengis.net/sld; xmlns:ogc=http://www.opengis.net/ogc; xmlns:xlink=http://www.w3.org/1999/xlink; xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance; xmlns:gml=http://www.opengis.net/gml; xsi:schemaLocation=http://www.opengis.net/sld http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd; NamedLayer NameUSA states/Name UserStyle Namepopulation/Name TitleUnited States/Title AbstractA Simple Brush Style/Abstract FeatureTypeStyle Rule LineSymbolizer Stroke GraphicStroke Graphic ExternalGraphic OnlineResource xlink:type=simple xlink:href=file:///Users/pramsey/green_bottom.gif / Formatimage/gif/Format /ExternalGraphic /Graphic /GraphicStroke CssParameter name=stroke-linecapround/CssParameter CssParameter name=stroke-dasharray1 0 1 0/CssParameter /Stroke /LineSymbolizer /Rule /FeatureTypeStyle /UserStyle /NamedLayer /StyledLayerDescriptor On Tue, Feb 28, 2012 at 12:00 PM, Robert Buckley robertdbuck...@yahoo.com wrote: Thanks for the advice. Unfortunately I don´t understand what you have just told mehalf-brush? half-circle? are these sld specifications?...I am delivering wms data using sld´s to style the data. could you please clarify? cheers, Rob Von: Paul Ramsey pram...@opengeo.org An: Robert Buckley robertdbuck...@yahoo.com; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 19:20 Dienstag, 28.Februar 2012 Betreff: Re: [postgis-users] how to create a hole in a polygon table? Rather than geometry processing, work on your rendering engine. You can usually achieve an interior band (the classic National Geographic political boundary, yes?) with a half-brush (rather than a full circle, use a half-circle), and just ensuring your polygons are consistently oriented. P. On Tue, Feb 28, 2012 at 10:04 AM, Robert Buckley robertdbuck...@yahoo.com wrote: Hi, I am trying to create an inner buffer of a polygon geometry table. Unfortunately this is the only way I can think of which will enable me to make a certain symbol for a polyon layer. The SLD Filter difference might do it, but it needs to be nested and i´m not sure if this is possible. anywayMy polygons need to be symbolized with a thin outline AND an inside Buffer of 3 mm(150m at a scale of 1:50 000)in essence just an inside ring. This means I need to first buffer my polygon geometry and then substract the buffer from the original polygon. = ST_Difference (i think!?!) my attempt created some geometries but not what I was expecting... create view rrop08.rrop_01_13_innerbuffer AS SELECT ST_Difference(a.st_buffer, b.the_geom) FROM rrop08.rrop_01_13_buffer a, rrop08.rrop_01_f b; rrop08.rrop_01_f = the original polyon rrop08.rrop_01_13_buffer = the inner buffer ___ 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 2.0.0beta1
Testers needed: PostGIS 2.0.0beta1: http://postgis.org/download/postgis-2.0.0beta1.tar.gz Current open tickets: http://trac.osgeo.org/postgis/report/22 Thanks! ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Tell your Story at FOSS4G NA
Hey all, I know I posted earlier about the deadline (which is tomorrow) for abstract submissions, but I wanted to stress how much the conference is looking for stories about folks putting these tools to use. Case studies, why open source worked for you, what your organization has gained from using open source tools... practical stuff, not even necessarily technical stuff. A story: we started here, we journeyed, there as a storm, we got through it, we arrived over there. Tell us your open source story: foss4g-na.org/abstract-submission/ Thanks! P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Raster Processing and ST_Transform
I'm trying to do some raster processing experimentation... - loaded CDED files (check) using a 256x256 tile size - reproject my data into UTM10 (check but...) First problem, after reprojection, all the chunks have different alignements (The two rasters provided do not have the same alignment) which is unfortunate. It would be nice to be able to ensure that when reprojecting all the objects get resampled into a same alignment (am I right that an alignment is a combination of a origin, pixel size and skewness?) so that they can be processed together in succeeding steps. I was hoping in my next steps to calculate slope and go from there, but I'm stopped at the transform step. Thoughts? While a general utility to re-sample any collection of rasters into the same basis would be good, multiple re-samplings are not great, so inherently re-sampling operations like st_transform should probably have a way to force the output alignment. P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial join not finishing
Without a query plan (explain ... ) there's not much people can do but scratch their chins sagely. P. On Wed, Feb 29, 2012 at 3:11 PM, Jorge Gil j.a.lopes...@tudelft.nl wrote: Hi everyone, I've been trying to do a spatial join for a couple of days in different ways and it never seems to finish. I've done something identical before in a much larger set of the same data and it worked after a few hours. I have a polygons (buildings) table with 150,000 records and a points (land use) table with 170,000 records. I want to calculate how many points are within each building and add that total to the column 'units' in the buildings table. The query I run is this: UPDATE buildings as aa SET units=bb.count FROM (SELECT cc.gid , count(*) from buildings as cc, landuse as dd where ST_Within(dd.the_geom, cc.the_geom) group by cc.gid) as bb WHERE aa.gid=bb.gid; I also try to create a separate table with the results of the join before doing the update, but the first step also fails. Am I doing something wrong? Is there a simple trick that makes this more efficient? Thank you, Jorge -- Jorge Gil PhD Candidate TU Delft / Faculty of Architecture Department of Urbanism Chair of Spatial Planning and Strategy Julianalaan 134 2628 BL Delft P.O. Box 5043 2600 GA Delft The Netherlands www.tudelft.nl ___ 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] Raster Processing and ST_Transform
On Wed, Feb 29, 2012 at 4:41 PM, Pierre Racine pierre.rac...@sbf.ulaval.ca wrote: First problem, after reprojection, all the chunks have different alignements (The two rasters provided do not have the same alignment) which is unfortunate. It would be nice to be able to ensure that when reprojecting all the objects get resampled into a same alignment (am I right that an alignment is a combination of a origin, pixel size and skewness?) so that they can be processed together in succeeding steps. Alignment is the combination of any arbitrary pixel corner on a raster grid, scale (what some call pixel size) and skewness. We are discussing about that with Bborie. He might come up with a slight redesign of ST_Transform. Right now you can achieve what you want with ST_Resample if I understand well. I was hoping in my next steps to calculate slope and go from there, but I'm stopped at the transform step. Thoughts? While a general utility to re-sample any collection of rasters into the same basis would be good, multiple re- samplings are not great, so inherently re-sampling operations like st_transform should probably have a way to force the output alignment. I think we all agree on that. BTW You will have to ST_Union everything before passing it to ST_Slope as all our neighboring functions do not transparently work on a tiled coverage. For now, calling ST_Slope on a tiles coverage should result in tile border effects. The way to get all the neighboring functions to work on tiled coverage is still an open question. Well, I cleverly unioned each tile with its neighbors before sloping, then clipped them back using the original extent, at least that's the theory, but the process never finished, which seemed unacceptable given the data size. Still learning! P. Pierre ___ 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] ST_LineToCurve for less than 4 points
No. This is a misunderstanding of purposes. ST_LineToCurve is the inverse of ST_CurveToLine. CurveToLine takes in a circular object and linearizes it, turning curves into regular small sections of linear arcs to approximate the input shape. LineToCurve analyzes an input linestring, looking for the tell-tale regularity of linearized arcs and converts them back to real arcs. The process can only work with linestrings that have for or more vertices to analyze. What Denis is looking for is probably something more like http://trac.osgeo.org/postgis/ticket/1291 P. On Tue, Feb 28, 2012 at 12:38 AM, Sandro Santilli s...@keybit.net wrote: On Tue, Feb 28, 2012 at 09:36:34AM +0100, Denis Rouzaud wrote: Hi, Can someone tells me why ST_LineToCurve needs at least a linestring of 4 points to be run? If I have three points, I should be able to create a circularstring, right? Here the error I have: SELECT ST_LineToCurve(ST_GeomFromText('LINESTRING(554803.177682475 145390.853708235,554796.469135702 145401.404724093,554769.634948609 145404.042478058)')); ERROR: pta_desegmentize needs implementation for npoints 4 If I read the message correctly it answers exactly your questions. It needs implementation. A patch is welcome. --strk; ,--o-. | __/ | Delivering high quality PostGIS 2.0 ! | / 2.0 | http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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 create a hole in a polygon table?
Rather than geometry processing, work on your rendering engine. You can usually achieve an interior band (the classic National Geographic political boundary, yes?) with a half-brush (rather than a full circle, use a half-circle), and just ensuring your polygons are consistently oriented. P. On Tue, Feb 28, 2012 at 10:04 AM, Robert Buckley robertdbuck...@yahoo.com wrote: Hi, I am trying to create an inner buffer of a polygon geometry table. Unfortunately this is the only way I can think of which will enable me to make a certain symbol for a polyon layer. The SLD Filter difference might do it, but it needs to be nested and i´m not sure if this is possible. anywayMy polygons need to be symbolized with a thin outline AND an inside Buffer of 3 mm(150m at a scale of 1:50 000)in essence just an inside ring. This means I need to first buffer my polygon geometry and then substract the buffer from the original polygon. = ST_Difference (i think!?!) my attempt created some geometries but not what I was expecting... create view rrop08.rrop_01_13_innerbuffer AS SELECT ST_Difference(a.st_buffer, b.the_geom) FROM rrop08.rrop_01_13_buffer a, rrop08.rrop_01_f b; rrop08.rrop_01_f = the original polyon rrop08.rrop_01_13_buffer = the inner buffer ___ 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 create a hole in a polygon table?
I was thinking of something like this, but it doesn't work. Unfortunately Geoserver doesn't have the ability to create offset lines, so there's no nice way to do this without doing a little negative buffer on the shape (which incidentally, you could do using the geometry transformation facility on the fly http://docs.geoserver.org/stable/en/user/styling/sld-extensions/geometry-transformations.html P ?xml version=1.0 encoding=ISO-8859-1? StyledLayerDescriptor version=1.0.0 xmlns=http://www.opengis.net/sld; xmlns:ogc=http://www.opengis.net/ogc; xmlns:xlink=http://www.w3.org/1999/xlink; xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance; xmlns:gml=http://www.opengis.net/gml; xsi:schemaLocation=http://www.opengis.net/sld http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd; NamedLayer NameUSA states/Name UserStyle Namepopulation/Name TitleUnited States/Title AbstractA Simple Brush Style/Abstract FeatureTypeStyle Rule LineSymbolizer Stroke GraphicStroke Graphic ExternalGraphic OnlineResource xlink:type=simple xlink:href=file:///Users/pramsey/green_bottom.gif / Formatimage/gif/Format /ExternalGraphic /Graphic /GraphicStroke CssParameter name=stroke-linecapround/CssParameter CssParameter name=stroke-dasharray1 0 1 0/CssParameter /Stroke /LineSymbolizer /Rule /FeatureTypeStyle /UserStyle /NamedLayer /StyledLayerDescriptor On Tue, Feb 28, 2012 at 12:00 PM, Robert Buckley robertdbuck...@yahoo.com wrote: Thanks for the advice. Unfortunately I don´t understand what you have just told mehalf-brush? half-circle? are these sld specifications?...I am delivering wms data using sld´s to style the data. could you please clarify? cheers, Rob Von: Paul Ramsey pram...@opengeo.org An: Robert Buckley robertdbuck...@yahoo.com; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 19:20 Dienstag, 28.Februar 2012 Betreff: Re: [postgis-users] how to create a hole in a polygon table? Rather than geometry processing, work on your rendering engine. You can usually achieve an interior band (the classic National Geographic political boundary, yes?) with a half-brush (rather than a full circle, use a half-circle), and just ensuring your polygons are consistently oriented. P. On Tue, Feb 28, 2012 at 10:04 AM, Robert Buckley robertdbuck...@yahoo.com wrote: Hi, I am trying to create an inner buffer of a polygon geometry table. Unfortunately this is the only way I can think of which will enable me to make a certain symbol for a polyon layer. The SLD Filter difference might do it, but it needs to be nested and i´m not sure if this is possible. anywayMy polygons need to be symbolized with a thin outline AND an inside Buffer of 3 mm(150m at a scale of 1:50 000)in essence just an inside ring. This means I need to first buffer my polygon geometry and then substract the buffer from the original polygon. = ST_Difference (i think!?!) my attempt created some geometries but not what I was expecting... create view rrop08.rrop_01_13_innerbuffer AS SELECT ST_Difference(a.st_buffer, b.the_geom) FROM rrop08.rrop_01_13_buffer a, rrop08.rrop_01_f b; rrop08.rrop_01_f = the original polyon rrop08.rrop_01_13_buffer = the inner buffer ___ 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] FOSS4G North America Call for Presentations Closes March 1
FOSS4G-NA (April 10-12, in Washington DC) is shaping up to be very successful: nearly half of our 400 registration slots are already filled, and lots of sponsors have signed on. Now it's up to you to bring the magic! If you are planning to speak at FOSS4G-NA, you must submit a presentation abstract by March 1. Please don't forget! http://foss4g-na.org/abstract-submission/ The conference program is being organized around three tracks: * open source technology best practices and tips; * deployment case studies; and * emerging ideas and trends in open source. We have space for about 50 presentations and 20 Ignite sessions, so get your submissions in! For more information, please visit our site at http://foss4g-na.org/. Space is limited to 300 attendees only, so you should sign up today to ensure that you can be a part of this exciting event! Related Links: * http://foss4g-na.org/registration/ FOSS4G-NA Registration * http://wiki.osgeo.org/wiki/North_America_Regional/ OSGeo North America Chapter * http://www.osgeo.org/ Open Source Geospatial Foundation * http://2012.foss4g.org/ FOSS4G 2012 - Beijing, China ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Calling pgsql2shp.exe via python
Your quotes are misplaced, try os.system('start C:\Program Files (x86)\PostgreSQL\9.1\bin\pgsql2shp.exe -f D:\testShapefile.shp -h localhost -u postgres -p 5432 test_db SELECT * FROM myschema.testquery') On Mon, Feb 20, 2012 at 2:24 PM, David Quinn daithiqu...@gmail.com wrote: I'm trying to call the pgsql2shp.exe function using python, as I would like to be able to loop through some tables. What I've tried doing is the following: import os os.system('start C:\Program Files (x86)\PostgreSQL\9.1\bin\pgsql2shp.exe -f D:\testShapefile.shp -h localhost -u postgres -p 5432 test_db SELECT * FROM myschema.testquery') When I paste the line: C:\Program Files (x86)\PostgreSQL\9.1\bin\pgsql2shp.exe -f D:\testShapefile.shp -h localhost -u postgres -p 5432 test_db SELECT * FROM myschema.testquery it produces a shapefile without a problem. However, my python script flashes open a CMD terminal, but it disappears before I see what is happening and does not produce any output. I'm using PostGIS 2.0, Windows 7, and Python 2.6.5. If there is an alternative approach to python for this, I will happily try it out. (I hope this question is not too off-topic!) Thanks, David ___ 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] Bogus unknown geometry error from ST_Transform
A bug is unspecified or unexpected behavior, and I'm afraid your case is neither: the system is operating exactly as I would expect. You have outlined what might be considered a legitimate enhancement, but it's on the edge, because do-what-I-mean-not-what-I-say behavior can cause other people problems from time to time. P. On Sun, Feb 19, 2012 at 12:29 PM, Aren Cambre a...@arencambre.com wrote: OK, I figured out the second error. The TIGER places dataset for Texas has both POLYGON and MULTIPOLYGON types, hence violating the * enforce_geotype_the_geom_3081* constraint. However, I think the original issue is a bug. If the geometry object has no SRID specified, as in my case, then ST_Transform should use whatever's specified in *public.geometry_columns*. I should not be getting errors about SRID of -1 if something is specified in *public.geometry_columns*. Aren On Sun, Feb 19, 2012 at 9:13 AM, Aren Cambre a...@arencambre.com wrote: Nope. BTW, there is an extraneous parenthesis in your example. Here's what I used: *UPDATE gis.gz_2010_48_160_00_500k* *SET the_geom_3081 = ST_Transform(ST_Setsrid(the_geom, 4629),3081);* (BTW, I said 4326 earlier, but I should have been using 4269. Not sure that explains the errors, however.) Now I get this: *ERROR: new row for relation gz_2010_48_160_00_500k violates check constraint enforce_geotype_the_geom_3081* This is just bizarre because the source and destination column are POLYGON types. Here's what I get when running *ST_AsEWKT(the_geom)*: *POLYGON((-94.940569 29.330815,-94.93334 29.329038,-94.930971 29.327708,-94.929679 29.32634,-94.938834 29.320412,-94.946644 29.323065,-94.946988 29.326066,-94.94085 29.330469,-94.940569 29.330815)) * I just double-checked, and there are no other rows in public.geometry_columns describing anything in the * gz_2010_48_160_00_500k* table besides the two rows I pasted earlier. Here's that constraint: *enforce_geotype_the_geom_3081 CHECK (geometrytype(the_geom_3081) = 'POLYGON'::text OR the_geom_3081 IS NULL);* Aren On Sun, Feb 19, 2012 at 2:10 AM, pcr...@pcreso.com wrote: Does this work? *UPDATE gis.gz_2010_48_160_00_500k* *SET the_geom_3081 = ST_Transform((ST_Setsrid(the_geom, 4326),3081);* ie:: is the problem finding the srid from geometry_columns or finding it but ST_Transform() fails even when given the srid? Even if there is a bug, this may work for you until fixed? Brent Wood --- On *Sun, 2/19/12, René Romero Benavides ichbinr...@gmail.com*wrote: From: René Romero Benavides ichbinr...@gmail.com Subject: Re: [postgis-users] Bogus unknown geometry error from ST_Transform To: postgis-users@postgis.refractions.net Date: Sunday, February 19, 2012, 8:37 PM El 18/02/2012 11:29 p.m., Aren Cambre escribió: ST_Transform is returning a bogus error of* Input geometry has unknown (-1) SRID*. Here's the query: *UPDATE gis.gz_2010_48_160_00_500k* *SET the_geom_3081 = ST_Transform(the_geom, 3081);* I said bogus because the database's *public.geometry_columns* table has valid values for these two columns, and neither has SRID of -1: *34281;'';gis;gz_2010_48_160_00_500k;the_geom;2;4326;POLYGON* * 34276;'';gis;gz_2010_48_160_00_500k;the_geom_3081;2;3081;POLYGON * What gives? Aren ___ postgis-users mailing listpostgis-us...@postgis.refractions.net http://mc/compose?to=postgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users I thought a SRID of -1 was a synonym for the default one (4326). But don't take my word for it, I'm beginning with postgis. -- http://sharingtechknowledge.blogspot.com/ -Inline Attachment Follows- ___ postgis-users mailing list postgis-users@postgis.refractions.nethttp://mc/compose?to=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] Bogus unknown geometry error from ST_Transform
As of 2.0 the whole question will be moot, as geometry_columns will be a view and will accurately reflect the state of the tables... so a table with no known srid will show up with unknown srid, and a table with known srid will have an entry... but since it will not be possible to insert an unknown srid geometry into a known srid table, your case won't ever arise. P. On Sun, Feb 19, 2012 at 1:00 PM, Aren Cambre a...@arencambre.com wrote: I hear you and am OK with reclassifying as an enhancement. However, what's the purpose of public.geometry_columns? If it's just ephemeral, then I accept your do-what-I-mean-not-what-I-say point. If it's supposed to be accurate metadata, then if some functionality needs this metadata *and* its missing from the geometry field, why not consult public.geometry_columns? Aren On Sun, Feb 19, 2012 at 2:35 PM, Paul Ramsey pram...@opengeo.org wrote: A bug is unspecified or unexpected behavior, and I'm afraid your case is neither: the system is operating exactly as I would expect. You have outlined what might be considered a legitimate enhancement, but it's on the edge, because do-what-I-mean-not-what-I-say behavior can cause other people problems from time to time. P. On Sun, Feb 19, 2012 at 12:29 PM, Aren Cambre a...@arencambre.comwrote: OK, I figured out the second error. The TIGER places dataset for Texas has both POLYGON and MULTIPOLYGON types, hence violating the * enforce_geotype_the_geom_3081* constraint. However, I think the original issue is a bug. If the geometry object has no SRID specified, as in my case, then ST_Transform should use whatever's specified in *public.geometry_columns*. I should not be getting errors about SRID of -1 if something is specified in *public.geometry_columns* . Aren On Sun, Feb 19, 2012 at 9:13 AM, Aren Cambre a...@arencambre.comwrote: Nope. BTW, there is an extraneous parenthesis in your example. Here's what I used: *UPDATE gis.gz_2010_48_160_00_500k* *SET the_geom_3081 = ST_Transform(ST_Setsrid(the_geom, 4629),3081);* (BTW, I said 4326 earlier, but I should have been using 4269. Not sure that explains the errors, however.) Now I get this: *ERROR: new row for relation gz_2010_48_160_00_500k violates check constraint enforce_geotype_the_geom_3081* This is just bizarre because the source and destination column are POLYGON types. Here's what I get when running *ST_AsEWKT(the_geom)*: *POLYGON((-94.940569 29.330815,-94.93334 29.329038,-94.930971 29.327708,-94.929679 29.32634,-94.938834 29.320412,-94.946644 29.323065,-94.946988 29.326066,-94.94085 29.330469,-94.940569 29.330815)) * I just double-checked, and there are no other rows in public.geometry_columns describing anything in the * gz_2010_48_160_00_500k* table besides the two rows I pasted earlier. Here's that constraint: *enforce_geotype_the_geom_3081 CHECK (geometrytype(the_geom_3081) = 'POLYGON'::text OR the_geom_3081 IS NULL);* Aren On Sun, Feb 19, 2012 at 2:10 AM, pcr...@pcreso.com wrote: Does this work? *UPDATE gis.gz_2010_48_160_00_500k* *SET the_geom_3081 = ST_Transform((ST_Setsrid(the_geom, 4326),3081); * ie:: is the problem finding the srid from geometry_columns or finding it but ST_Transform() fails even when given the srid? Even if there is a bug, this may work for you until fixed? Brent Wood --- On *Sun, 2/19/12, René Romero Benavides ichbinr...@gmail.com*wrote: From: René Romero Benavides ichbinr...@gmail.com Subject: Re: [postgis-users] Bogus unknown geometry error from ST_Transform To: postgis-users@postgis.refractions.net Date: Sunday, February 19, 2012, 8:37 PM El 18/02/2012 11:29 p.m., Aren Cambre escribió: ST_Transform is returning a bogus error of* Input geometry has unknown (-1) SRID*. Here's the query: *UPDATE gis.gz_2010_48_160_00_500k* *SET the_geom_3081 = ST_Transform(the_geom, 3081);* I said bogus because the database's *public.geometry_columns*table has valid values for these two columns, and neither has SRID of -1: * 34281;'';gis;gz_2010_48_160_00_500k;the_geom;2;4326;POLYGON* * 34276;'';gis;gz_2010_48_160_00_500k;the_geom_3081;2;3081;POLYGON * What gives? Aren ___ postgis-users mailing listpostgis-us...@postgis.refractions.net http://mc/compose?to=postgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users I thought a SRID of -1 was a synonym for the default one (4326). But don't take my word for it, I'm beginning with postgis. -- http://sharingtechknowledge.blogspot.com/ -Inline Attachment Follows- ___ postgis-users mailing list postgis-users@postgis.refractions.nethttp://mc/compose?to=postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users
Re: [postgis-users] OK to have polygon and multipolygon in same geometry field?
No harm from a PostGIS PoV. External software that wants type homogeneity might complain, perhaps. P. On Sun, Feb 19, 2012 at 1:04 PM, Aren Cambre a...@arencambre.com wrote: I have a PostGIS dataset imported from a TIGER places SHP for Texas (gz_2010_48_160_00_500k). I imported using QGIS's supposedly-soon-to-be-deprecated SPIT tool. The import causes the the_geom field to have both polygon and multipolygon types. Any harm with this? I ultimately want to use this table's the_geom field to find points within them that are in another table. Aren ___ 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] [Building PostGIS2.0] undefined reference to `libintl_printf'
I feel like in the link step the mingw is happier when you put static libraries at the end of the link line. Just a feeling though. P. On Thu, Feb 16, 2012 at 10:19 AM, GeoFox edison@ngc.com wrote: Hi everyone, I got the following error while building (making) PostGIS 2.0: *** Making all in loader make[1]: Entering directory `/c/projects/postgis/postgis-2.0.0alpha5SVN/loader' /bin/sh ..//libtool --mode=link gcc -g -O2 -DDLL_EXPORT -DPIC -I/c/projects/pg/pg91/include -mwindows shpopen.o dbfopen.o shp2pgsql-core.o shpcommon.o shp2pgsql-gui.o getopt.o safileio.o pgsql2shp-core.o ../liblwgeom/liblwgeom.la shp2pgsql-gui.res -o shp2pgsql-gui.exe -Lc:/gtkbundle/lib -lgtk-win32-2.0 -lgdk-win32-2.0 -latk-1.0 -lgio-2.0 -lpangowin32-1.0 -lgdi32 -lpangocairo-1.0 -lgdk_pixbuf-2.0 -lpango-1.0 -lcairo -lgobject-2.0 -lgmodule-2.0 -lgthread-2.0 -lglib-2.0 -lintl -liconv -Lc:/projects/pg/pg91/lib -lpq -lintl gcc -g -O2 -DDLL_EXPORT -DPIC -I/c/projects/pg/pg91/include -mwindows shpopen.o dbfopen.o shp2pgsql-core.o shpcommon.o shp2pgsql-gui.o getopt.o safileio.o pgsql2shp-core.o shp2pgsql-gui.res -o shp2pgsql-gui.exe ../liblwgeom/.libs/liblwgeom.a -L/c/projects/geos/rel-3.3.2/lib /c/projects/geos/rel-3.3.2/lib/libgeos_c.dll.a /c/projects/geos/rel-3.3.2/lib/libgeos.dll.a -L/c/projects/pg/pg91/lib /c/projects/pg/pg91/lib/libproj.a -Lc:/gtkbundle/lib -lgtk-win32-2.0 -lgdk-win32-2.0 -latk-1.0 -lgio-2.0 -lpangowin32-1.0 -lgdi32 -lpangocairo-1.0 -lgdk_pixbuf-2.0 -lpango-1.0 -lcairo -lgobject-2.0 -lgmodule-2.0 -lgthread-2.0 -lglib-2.0 -Lc:/projects/pg/pg91/lib -lpq /mingw/lib/libintl.dll.a /c/mingw/lib/libiconv.dll.a -L/c/projects/geos/rel-3.3.2/lib -L/c/mingw/lib -L/mingw/lib -L/c/projects/geos/rel-3.3.2/lib -L/c/mingw/lib -L/mingw/lib shp2pgsql-gui.o: In function `usage': c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3435: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3436: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3437: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3438: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3439: undefined reference to `libintl_printf' shp2pgsql-gui.o:c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3440: more undefined references to `libintl_printf' follow collect2: ld returned 1 exit status make[1]: *** [shp2pgsql-gui.exe] Error 1 make[1]: Leaving directory `/c/projects/postgis/postgis-2.0.0alpha5SVN/loader' make: *** [all] Error 1 *** Does anyone have any idea how to fix it? Thank you so much! - Edison Tam NGC SWE US -- View this message in context: http://postgis.17.n6.nabble.com/Building-PostGIS2-0-undefined-reference-to-libintl-printf-tp4477049p4477049.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
Re: [postgis-users] ST_Crosses doesn't use index?
Stefan, This has to do with your data / configuration, not ST_Crosses, as the function clearly includes an index call. CREATE OR REPLACE FUNCTION ST_Crosses(geometry,geometry) RETURNS boolean AS 'SELECT $1 $2 AND _ST_Crosses($1,$2)' LANGUAGE 'SQL' IMMUTABLE; P. On Thu, Feb 16, 2012 at 3:51 PM, Stefan Keller sfkel...@gmail.com wrote: Hi ST_Crosses doc says that it uses the spatial index (if available). But I'm afraid it's not. I'm using PostGIS 1.5 on Linux. -- as it should be: SELECT name FROM osm_line a, osm_line b WHERE a.osm_id != b.osm_id AND ST_Crosses(a.way, b.way) -- takes longer than 60 sec, 586 features. -- now with ST_Contains: SELECT name FROM osm_line a, osm_line b WHERE ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,a.way) AND ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,b.way) AND a.osm_id != b.osm_id AND ST_Crosses(a.way, b.way) -- takes 3.5sec, 586 features. Obviously ST_Crosses does not consider the geometry index. Why this? Yours, Stefan ___ 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