[postgis-users] pgsql2shp encoding problem
G'day all, I'm having a problem exporting data from a utf8 database to shape files for a client. They are receiving the data and viewing it only to find that characters with diacritics are all messed up. My understanding was that .dbf was originally ascii only (and the column headings still are) but now the .dbf file can contain data in any encoding. So with the suggestion of nicolas ribot, I am exporting using : export PGCLIENTENCODING=LATIN9; pgsql2shp -f etc etc I also read in a number of sites that this can be specified by adding a file with a .cpg suffix, and including the name of the encoding for the .dbf in it, i.e. LATIN9 Presumably, one would end up with : data.cpg data.dbf data.prj data.qix data.shp data.shx Can anyone advise me if this is true and reasonable? Or more importantly, will work with ArcGIS and / or MapServer? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] One-click installer or building from the source code (postgresql)
G'day Muni, A good rule for getting an answer on a mailing list is to ask a clear question - if you don't get a response after three weeks you might want to consider rephrasing it, or being more specific. You have provided very little information on what you are actually trying to achieve, or on what sort of system, what constraints you are working under or what scale and type of data you are managing. For a start, typing postgresql arcsde into google returned at least 60 000 records, the first ten of which included an ESRI supplied installation guide and a number of blogs and email lists discussing this combination, I would suggest you try this route, and then bring in some specific questions. good luck Ben On 15/05/2012, at 2:14 AM, Melpati, Muni wrote: Can someone atleast provide references to previous studies with regards to using a combination of postgresql, PostGIS and ArcSDE products for maintaining GIS and raster data. 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] topology performance improvement purpose
Do you need to even change the search_path - surely you could have a function version with the 'topology' schema hard-coded for the performance improvement. Obviously that won't work if you have a different schema name, but if the performance enhancement is worth it, grepping out topology to whatever you need will also be worth the effort. cheers Ben On 03/05/2012, at 5:58 AM, Jose Carlos Martinez Llario wrote: Hi, wanted to share a though about improving the topology performance in postgis. Many of the functions are using EXECUTE instead of running directly select commands. In many cases this is need just because of the used of the schema name of the topology primitive tables. I found between 4 and 10 times improving performance some sentences. I guessing it could be even better. My question is if an approach like the following makes sense: 1.- With one command we can make the current topology schema the default schema in search path, 2.- then working with topology 3.- Change search_path again when finishing the spatial analysis. (the parameter of the topology name in the functions should be ignored or removed) I know it requires a lot of changes but currently the performance of the persistent topology is too slow. if one do that many functions can be run without EXECUTE. Spatial analysis between different topologies is not possible which fits the procedure. What do you think Sandro? 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
Re: [postgis-users] Size of a multipolygon
Thanks Brent, Stephen, Clearly the answer was much to straightforward for me to find!! I blame a poorly constructed question. Thank you. On 02/05/2012, at 1:06 PM, pcr...@pcreso.com wrote: FYI, I was interested in this post, as a PL/R user it may interest you as well? http://www.spatialdbadvisor.com/postgis_tips_tricks/290/r-based-delaunay-triangulation-function-for-postgis-using-the-deldir-package Nice - I've used Regina's script before a couple of times, but I haven't had to worry about three dimensions. Once to define approximate postcode boundaries for Australia - it's possible to obtain a list of postcode points, but Australia Post specifically declines to produce a map showing which areas are covered by which postcodes (mainly because they are not simply distributed by distance - some people get their mail by plane once a week, a live over 1000km from 'their' post-office. But they are outliers from an epidemiological perspective. Also for mobile phone tower coverage - then truncating all the polygons at 35km from the centroid. We now use PL/R almost exclusively for data output (except for interactive maps) on web sites - including tables. cheers Ben Cheers, Brent --- On Wed, 5/2/12, Ben Madin li...@remoteinformation.com.au wrote: From: Ben Madin li...@remoteinformation.com.au Subject: [postgis-users] Size of a multipolygon To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Wednesday, May 2, 2012, 3:26 PM Hello all, I'm sure the answer is simple, but I'm lost on what to search on - I need to quickly search through several thousand multi polygons for any which have more than one part making them up. What is the best way to do this…? (I've tried array_length(), but don't seem to be able to feed it the right part. I can't believe I can't find a function that does it… or am I looking at dumping the multi polygons into a new table and counting the number of lines for each one... cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Size of a multipolygon
Hello all, I'm sure the answer is simple, but I'm lost on what to search on - I need to quickly search through several thousand multi polygons for any which have more than one part making them up. What is the best way to do this…? (I've tried array_length(), but don't seem to be able to feed it the right part. I can't believe I can't find a function that does it… or am I looking at dumping the multi polygons into a new table and counting the number of lines for each one... cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Hi Puneet, Did it work when you removed the offending semicolon, as described in the syntax error message? cheers Ben On 06/03/2012, at 11:52 AM, Puneet Kishor wrote: On Mar 5, 2012, at 9:44 PM, Derek Jones wrote: Hi all, I have used an R solution that works well with the plsql to do this. Found here: http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02 Needed some mods for my local solution, but helpful. Yes, I tried that, but as I noted in my earlier email, I got the following error ERROR: R interpreter expression evaluation error DETAIL: Error in pg.spi.exec(sprintf(select x(%2$s) as x, y(%2$s) as y from %1$s;, : error in SQL statement : syntax error at or near ; CONTEXT: In R support function pg.spi.exec In PL/R function voronoi ___ 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] setting up or accessing a host
Yes - anytime you let anyone else connect to your machine it becomes a risk. You have to balance that with the usefulness of an isolated machine vs the value of being able to access it over a network. I guess in this case you are only allowing connections to the database, so that limits the damage that could happen. In this case, because you are letting anyone from that single ip address connect to any database, you may be pretty safe if you know the owner of that machine - and they are likely to be careful with their password. I'd be inclined towards : hostspecificdatabaseindividualuser machineIP/32 md5 and of course you could use hostssl if you were even more worried. Then it is a matter of what the individual user can access that becomes your concerns - for example, for most mapserver interfaces we have a 'mapuser' that can only select data from the necessary tables. I'm suprised that the fully qualified domain name of the machine is working - the documentation has : IP addresses can only be specified numerically, not as domain or host names cheers Ben On 15/02/2012, at 12:46 AM, Bistrais, Bob wrote: Hi Ben, I think I’ve had some success, thanks to your input. But please let me know if this is OK… I edited pg_hba.conf, adding a line like this: Host all all mymachinename md5 -where mymachinename is actually the fully qualified domain name of the machine. This works, in the sense that I can now display the data through other than localhost. But is this a good practice? Am I breaching security, or setting myself up for some other disaster? Thanks, Bob From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Tuesday, February 14, 2012 10:42 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] setting up or accessing a host Bob, the security is by default tight - but it sounds like you want host based authentication (hba) You will need to read this page a few times : http://www.postgresql.org/docs/9.1/interactive/auth-pg-hba-conf.html and then before you do anything else edit your postgresql.conf file (about 60 lines down) to look like : listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) Note the * in between the quotes (you may well have localhost there now). Then edit your pg_hba.conf file. note that you need to restart the server when you change the listen addresses, but you only need to reload it when you edit the pg_hba.conf file Also note that this can be really painful for users with dynamic ip address allocation. good luck. cheers Ben On 14/02/2012, at 11:04 PM, Bistrais, Bob wrote: So at this point I figured out how to get a PostGIS layer to display through MapServer, but that is only where host=localhost. Now I’m trying to figure out how to access the data from other machines. I’m looking through the documentation but haven’t found out how yet. Can anyone point me in the right direction? ___ 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 Manager QGIS Plugin error
Kevin, That sounds like a syntax error in the plugin itself. You probably need to contact the maintainer. I can't actually get it to work in QGIS 1.7.3 on OSX 10.6.8 cheers Ben On 16/02/2012, at 7:21 AM, Kevin Goulding wrote: I'm just now getting set up with a PostGIS database connected with QGIS. A few plugins are giving me errors when I try to connect to my database. I am using Mac OS X 10.6.8 and QGIS 1.8. When I try to connect to my PostGIS database using the PostGIS Manager plugin, I receive the following error: “Couldn’t connect to database: missing “=” after “‘” in connection info string”. Any thoughts? I will add that I have successfully added PostGIS layers to my project, but now want to start using pgRouting, so need to connect to the database. I receive the exact same error when attempting to use the pgRouting Layer plugin. Thanks! Kevin ___ 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] setting up or accessing a host
Bob, the security is by default tight - but it sounds like you want host based authentication (hba) You will need to read this page a few times : http://www.postgresql.org/docs/9.1/interactive/auth-pg-hba-conf.html and then before you do anything else edit your postgresql.conf file (about 60 lines down) to look like : listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) Note the * in between the quotes (you may well have localhost there now). Then edit your pg_hba.conf file. note that you need to restart the server when you change the listen addresses, but you only need to reload it when you edit the pg_hba.conf file Also note that this can be really painful for users with dynamic ip address allocation. good luck. cheers Ben On 14/02/2012, at 11:04 PM, Bistrais, Bob wrote: So at this point I figured out how to get a PostGIS layer to display through MapServer, but that is only where host=localhost. Now I’m trying to figure out how to access the data from other machines. I’m looking through the documentation but haven’t found out how yet. Can anyone point me in the right direction? ___ 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 Topology Pledge: completed !
Thanks strk,
this is great news - thank you for your efforts,
cheers
Ben
On 30/01/2012, at 7:49 PM, Sandro Santilli wrote:
As of revision 8963 (included in upcoming 2.0.0alpha3 [1]), the function
to convert simple layers to topologically defined layers [2] is completed.
[1] 2.0.0alpha3 http://www.postgis.org/download/
[2] toTopoGeom http://trac.osgeo.org/postgis/ticket/1017
This means that building a persistent topology for your public.states.geom
layer would be as simple as this:
SELECT CreateTopology('states_topo');
SELECT AddTopoGeometryColumn('states_topo',
'public', 'states', 'topogeom',
'POLYGON');
UPDATE public.states
SET topogeom = toTopoGeom(geom, 'states_topo');
You can then check correctness of the conversion:
SELECT gid FROM public.states WHERE NOT ST_Equals(geom, topogeom);
Look for area overlaps:
SELECT r1.element_id FROM states_topo.relation r1, states_topo.relation r2
WHERE r1.topogeo_id != r2.topogeo_id AND r1.element_id = r2.element_id;
Or underlaps:
SELECT face_id FROM istat_topo.face WHERE face_id 0
AND face_id NOT IN ( SELECT element_id FROM istat_topo.relation );
Perform any editing required to clean things up [3], or to simplify the edges.
You can take a look at the primitives with QGIS db_manager plugin [4], or even
at the actual TopoGeometries with QGIS master [5] (although it will be slow in
selecting features within the viewport, see ticket #1290 [6]).
[3] http://strk.keybit.net/blog/2011/11/21/topology-cleaning-with-postgis/
[4] qgis db_manager http://www.qgis.org/wiki/DB_Manager_plugin_GSoC_2011
[5] QuantumGIS http://www.qgis.org
[6] overlap TopoGeometry http://trac.osgeo.org/postgis/ticket/1290
And of course you can convert TopoGeometries back to simple geometries when
needed
for performance or compatibility reasons:
ALTER TABLE public.states ADD newgeom geometry;
UPDATE public.states SET newgeom = topogeom::geometry;
Happy edge walking!
http://strk.keybit.net/blog/2012/01/28/a-walk-on-the-wild-side/
C L O S I N GC R E D I T S
I was able to dedicate my time to the implementation of the toTopoGeom
function thanks to the contribution of a disparate group of people and
companies putting a part of the money each to reach the bigger target:
Andrea PeriAnne Ghisla R3 GIS
Silvio Grosso GFOSS (gfoss.it) Cooperativa Alveo
AusVet Ingvild Nystuen Luca S. Percich
Richard Greenwod Andreas Neumann Oslandia
A special thank goes to Andrea Peri for his initial kick-starter contribution
which allowed me to set an affordable target for the pledge.
Also thanks to the Geographical Free and Open Source Software association
(GFOSS) for the help with reducing paperwork involved in handling all the
contributions.
--strk;
,--o-.
| __/ |Thank you for PostGIS-2.0 Topology !
| / 2.0 |http://www.pledgebank.com/postgistopology
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au/
This transmission is for the intended addressee only and anyone else subscribed
to this mailing list. Clearly then it is not confidential information. If you
have received this transmission in error, sorry. The contents of this email are
the opinion of the writer only and are not endorsed by AusVet Animal Health
Services unless expressly stated otherwise. Although AusVet uses virus scanning
software we do not accept liability for viruses or similar in any attachments.
Congratulations on reading this boring and probably completely unnecessary bit
- you may be the only person ever to do so!
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] One-to-many join
John, I'm no expert at this, but assuming that your query returns multiple rows, I suspect this is to do with the WMS layer only identifying one feature (which then only returns one of row). This would seem like standard WMS behaviour. If you have multiple features at the same point, you might need to use WFS. If the query returns multiple rows, you are probably better to ask this on the MapServer list. There may be better ways to do this, but you may need to find the location and provide the multiple rows of data through a secondary query into another window or layer in the window. What mechanism are you using for displaying the maps and identifying - ie qgis, web template, openlayers?? cheers Ben On 31/01/2012, at 11:00 AM, John Morgan wrote: Hello, I am attempting to get a one (polys) to many (table) to load postgis data within a mapserver wms. I have the following defined in the .map file. DATA the_geom FROM (SELECT polys.gid AS gid, polys.the_g eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys RIGHT OUTER JOIN table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING SRID=4326 It does load, the layer, however, it doesn't seem to be performing the one-to-many for the attributes on identify. Thanks for any feedback. Cheers, Derek ___ 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 simple pt-in-poly lookups
Puneet, Trying to find which country a large set of points was in we have actually found it much quicker to find points in provinces (smaller polygons) and then return the country code associated with the province. No chopping anything, so I would guess you could use a world map, allocate a continent to each country in the world map and query it. The speed up we saw was (I guess) for the same reason - the bbox was efficient. cheers Ben On 21/12/2011, at 12:06 PM, Mr. Puneet Kishor wrote: On Dec 20, 2011, at 9:48 PM, Martin Davis wrote: For more detail check out this thread on the same issue: http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html Thanks. Chopping up my coverage into hundreds of small regions is the last avenue I want to try. Going by the text of that email, it seems that few, large, regions with many vertices (may be) the problem. I will try generalizing my continents so that I have few, large regions with *very few* vertices and see if that speeds up the SELECTs. On 12/20/2011 5:28 PM, Puneet Kishor wrote: On Dec 20, 2011, at 7:21 PM, Paul Ramsey wrote: Chop up the continents into smaller pieces. hmmm... I am not sure I understand the above. And then what? UNION each smaller piece query? On Tue, Dec 20, 2011 at 4:35 PM, Puneet Kishorpunk.k...@gmail.com wrote: This is probably a really basic question... my ST_Within or ST_Intersects selecting points in a continent are way too slow (both take upward of 200 secs). SELECT Count(c_id) FROM c, continents n WHERE ST_Intersects(c.the_geom, n.the_geom) AND n.continent = 'North America'; Both tables have gist indexes on the geometries. The above query has the following plan Aggregate (cost=9.66..9.67 rows=1 width=4) - Nested Loop (cost=0.00..9.66 rows=1 width=4) Join Filter: _st_intersects(c.the_geom, n.the_geom) - Seq Scan on continents n (cost=0.00..1.10 rows=1 width=32) Filter: ((continent)::text = 'North America'::text) - Index Scan using pbdb__collections_the_geom on collections c (cost=0.00..8.30 rows=1 width=104) Index Cond: (c.the_geom n.the_geom) The table c has approx 120K rows, and the continents table has 8 rows.Suggestions on how I can improve this? Yes, the computer is otherwise very swift and modern. -- Puneet Kishor ___ 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.1890 / Virus Database: 2109/4692 - Release Date: 12/20/11 ___ 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] Is st_isvalidreason singular for any polygon, or can multiple errors be returned?
I guess the answer to my question is in the question, but I've noticed that if I run a statement looking for not isvalid geometries, I get something like : =# SELECT gid, ccode, admin1, mapcode, st_mem_size(the_geom), st_isvalidreason(the_geom) FROM summ.polbnda WHERE st_isvalid(the_geom) IS FALSE ORDER BY 2, 3; NOTICE: Ring Self-intersection at or near point 123.055 10.9102 NOTICE: Ring Self-intersection at or near point 121.007 5.68846 gid | ccode | admin1 | mapcode | st_mem_size | st_isvalidreason -+---+---+-+-+- 120 | PHL | NEGROS OCCIDENTAL |3592 | 105387 | Ring Self-intersection[123.054838 10.91023] 84 | PHL | SULU |3556 | 212748 | Ring Self-intersection[121.007041 5.688462] (2 rows) at which time I zoom into the locations in QGIS and remove the offending part of the geometry. After fixing the two, I run the same query, and gid | ccode | admin1 | mapcode | st_mem_size | st_isvalidreason -+---+---+-+-+-- 120 | PHL | NEGROS OCCIDENTAL |3592 | 105339 | Ring Self-intersection[123.310249 10.960445] (1 row) So again I go in a repair, and so it goes - It ended up taking about 8 iterations to fix everything. So my question is - when running st_isvalidreason does it just return the first invalid problem it finds, or do I need a more sophisticated query to return all the problem points? My motivation is that re-running the query is taking a long time (it's a very high resolution set of geometries), so it would be great to get all of the offenders so I could work around each polygon once. This in : POSTGIS=1.5.3 GEOS=3.3.1-CAPI-1.7.1 PROJ=Rel. 4.7.1, 23 September 2009 LIBXML=2.7.3 USE_STATS cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Is st_isvalidreason singular for any polygon, or can multiple errors be returned?
Thanks strk, Either way is fine, but maybe for now we could put a line into the documentation to that effect: Note: By design only the first invalidity in a polygon is reported. After correcting errors identified by st_isvalidreason it should be run again to check that there are no further errors. cheers Ben On 19/12/2011, at 9:52 PM, Sandro Santilli wrote: On Mon, Dec 19, 2011 at 09:20:09PM +0800, Ben Madin wrote: So my question is - when running st_isvalidreason does it just return the first invalid problem it finds, or do I need a more sophisticated query to return all the problem points? Only the first one. By design. It's being discussed on JTS list if this will change in the future. For now it is assumed that first invalidity would render later ones hard to detect. --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-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] Is st_isvalidreason singular for any polygon, or can multiple errors be returned?
No worries - is that just adding a ticket in trac, or is there something more sophisticated for documentation? cheers Ben On 19/12/2011, at 11:58 PM, Sandro Santilli wrote: On Mon, Dec 19, 2011 at 11:02:49PM +0800, Ben Madin wrote: Either way is fine, but maybe for now we could put a line into the documentation to that effect: Note: By design only the first invalidity in a polygon is reported. After correcting errors identified by st_isvalidreason it should be run again to check that there are no further errors. Sure, do you feel like producing a patch and attaching it to a ticket on http://trac.osgeo.org/postgis ? --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-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] Fastest test for point (nearly) in polygon
G'day all, I'm sure I've seen a similar discussion to this question, but I can't find it now. During a bulk data submission process we have a large number of point locations and the code of the province in which they are 'reputed' to be occurring. Having put some basic checks through the system we are inclined to check this at upload time, but have to realistically anticipate that points within a certain limit will be adequately accurate (at the moment about 2km, but probably doesn't have to be strictly 2km) There are any number of ways of doing this - all uploaded data (and geometries) are currently in EPSG 4283 (Australian Lat Long). Options would include using : st_distance_sphere(province polygon, st_setsrid(st_makepoint(long, lat), 4283)) 2000 st_dwithin (province polygon, st_setsrid(st_makepoint(long, lat), 4283), 0.02) st_within (st_buffer(province polygon, 0.02), st_setsrid(st_makepoint(long, lat), 4283)) st_intersects(province polygon, st_buffer(st_setsrid(st_makepoint(long, lat), 4283))) or any number of similar variants. Our problem is that there are many rows, and even prior to these checks, other checks on other aspects of the data were inclined to take longer than we would like. I can accept that trying to avoid to much transforming is probably better (and accepting some slightly inconsistent accuracy) from a speed perspective. Can anyone suggest which of these checks (or others) might be fastest? I'm leaning towards the latter two as they are mainly functions offered by GEOS, which I understand to be faster than PL functions. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Fastest test for point (nearly) in polygon
Thanks Stephen, that's very helpful. cheers Ben On 14/12/2011, at 10:47 AM, Stephen Woodbridge wrote: On 12/13/2011 9:38 PM, Ben Madin wrote: G'day all, I'm sure I've seen a similar discussion to this question, but I can't find it now. During a bulk data submission process we have a large number of point locations and the code of the province in which they are 'reputed' to be occurring. Having put some basic checks through the system we are inclined to check this at upload time, but have to realistically anticipate that points within a certain limit will be adequately accurate (at the moment about 2km, but probably doesn't have to be strictly 2km) There are any number of ways of doing this - all uploaded data (and geometries) are currently in EPSG 4283 (Australian Lat Long). Options would include using : st_distance_sphere(province polygon, st_setsrid(st_makepoint(long, lat), 4283)) 2000 this has to compute all distances regardless st_dwithin (province polygon, st_setsrid(st_makepoint(long, lat), 4283), 0.02) st_dwithin() is fastest and if you need it more accurately, then: st_dwithin(...) and st_distance_sphere(province polygon, st_setsrid(st_makepoint(long, lat), 4283)) 2000 st_within (st_buffer(province polygon, 0.02), st_setsrid(st_makepoint(long, lat), 4283)) don't buffer the polygon, always buffer the point and even faster than that use st_dwithin st_intersects(province polygon, st_buffer(st_setsrid(st_makepoint(long, lat), 4283))) or any number of similar variants. Our problem is that there are many rows, and even prior to these checks, other checks on other aspects of the data were inclined to take longer than we would like. I can accept that trying to avoid to much transforming is probably better (and accepting some slightly inconsistent accuracy) from a speed perspective. Can anyone suggest which of these checks (or others) might be fastest? I'm leaning towards the latter two as they are mainly functions offered by GEOS, which I understand to be faster than PL functions. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Undefined symbol list_length ? postgis 1.5.3 from FreeBSD ports
G'day all, trying to install (reinstall) postgis 1.5.3 into postgresql 9.0.4 on FreeBSD7, I am receiving this message : # \i /usr/local/share/postgis/contrib/postgis-1.5/postgis.sql SET BEGIN psql:/usr/local/share/postgis/contrib/postgis-1.5/postgis.sql:59: ERROR: could not load library /usr/local/lib/postgresql/postgis.so: dlopen (/usr/local/lib/postgresql/postgis.so) failed: /usr/local/lib/postgresql/postgis.so: Undefined symbol list_length I've never seen this one before, and Google is not being very helpful... I'm suspecting it is something about the configuration of postgresql, so I'm rebuilding it, but any ideas would be much appreciated. FWIW, this was all working previously, and I'm not quite sure why it has now stopped. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Undefined symbol list_length ? postgis 1.5.3 from FreeBSD ports
Actually, that did the trick - a complete clean, deinstall and reinstall of everything. cheers Ben On 25/11/2011, at 5:34 PM, Sandro Santilli wrote: On Fri, Nov 25, 2011 at 04:15:18PM +0800, Ben Madin wrote: G'day all, trying to install (reinstall) postgis 1.5.3 into postgresql 9.0.4 on FreeBSD7, I am receiving this message : # \i /usr/local/share/postgis/contrib/postgis-1.5/postgis.sql SET BEGIN psql:/usr/local/share/postgis/contrib/postgis-1.5/postgis.sql:59: ERROR: could not load library /usr/local/lib/postgresql/postgis.so: dlopen (/usr/local/lib/postgresql/postgis.so) failed: /usr/local/lib/postgresql/postgis.so: Undefined symbol list_length Sounds like a mismatch between PostgreSQL headers and PostgreSQL library. Do you have two versions of PostgreSQL ? Did you reconfigure postgis source code after upgrading postgresql ? --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ 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] Installing PostGIS on FreeBSD : trouble with libpq
Which version of PostgreSQL did you install? cheers Ben On 24/11/2011, at 4:47 PM, dhee...@dheerajchand.com wrote: Mark Stosberg mark at summersault.com writes: On 10/27/2011 08:34 PM, Dheeraj Chand wrote: Hi, all, I'm not that great with BSD, but I'm running into this error trying to install from the ports tree. Anyone willing to help? What version of FreeBSD are you using? Did you install PostgreSQL 8.4.9 from ports, a package, or by hand? Mark ACK! I never got this email. Stupid junk filters. It all came from ports. I've done nothing by hand. Dheeraj ___ 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] trouble shp2pgsql with numeric
Tonton,
This is a limitation of shapefiles .dbf format. (yet another reason to move
away from shapefiles). PostGIS can't assume that your value is a categorical
value (ie a label) when the column only contains numbers and is tagged as
numeric. To allow for the widest range of cases, it is imported probably as
float or numeric, so if all your column values are integer you could easily
(post-import) issue :
ALTER TABLE ltb ALTER COLUMN annoying_column TYPE int USING
cast(annoying_column as int);
hth.
cheers
Ben
On 22/11/2011, at 5:20 PM, Tonton wrote:
hello
i use shp2pgsql in a web mapping application to add shp file into postgis.
after uploading on the server side (in django framwork) i use this cmd
cmd = /usr/bin/shp2pgsql -c -s 4326 -W latin1 -g geometry
+/pathToSHPname+ +str(tablename)++/pathToMyFile.sql
it is working near great now but for some numeric information in the sql
request numbers are transform to sort of float : 2 became 2.00
this is an exemple for my generate sql !
INSERT INTO ltb
(coef_conge,depart_lig,fc,freq_moy,freq__0,freq_moy_m,id_aire,id_station,libbelle,libelle,libell_,ligne,mode,num_type,nom_ligne,nom_statio,num_ligne,num_ligne_,sens,tps_parc,terminus_l,tps_parcou,type_acces,v_com_sytr,vitesse,id_lgn,nom_lgn,nom_stt,order,tpsatt_lgn,vit_off_km,vitcom_lgn,vitesse_km,length,geometry)
VALUES
('0.00',NULL,'104','0.00','0','0.00','0','0',NULL,NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0.00','0','18226',NULL,NULL,'31','0','0.00','0.00','0.00','222.2906997126','010520E6100100010220E6100900E721D9B6305F1340E073BE5D7BF34640319802F44B5F13402462B73D81F34640B40C3CD08B5F1340C2FD98C68BF34640C8A0171BA45F134083D387CD8EF34640DBF078F7D05F13408FF4362192F346404248D110246013400742949695F34640BEA067CCB56013403119CEC098F346407A9371A6FD601340F5DF664A9BF34640EA04C7B160611340602B
BB52A1F34640');
it is disapointing for me because sometimes the use of these data is for
label or legend and it is not great to see 2.000 instead of 2 on map
or in legend part
does someone have same trouble or idea to resolv the trouble ?
regards
T.
___
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] are there any tools recommended to create a route network?
Peng, Did you have a look at the project strk recommended to you on the 19th July on this list? It would seem to answer your question? cheers Ben On 21/11/2011, at 10:28 AM, sunpeng wrote: Hi, friends, I know ArcCatlog could generate a rout network from a shp file, are there any other tools recommended to create a route network? Thanks! peng ___ 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 Day cards
Perfect - just what I need for my original suggestion. Now to find the scissors and the kids and the scotch! cheers Ben On 17/11/2011, at 10:07 AM, Paragon Corporation wrote: Well GIS Day is just about over and Post GIS day is slowly arriving around the world. To celebrate this new day, we have prepared some Post GIS playing cards. http://www.postgis.us/post_gis_day_cards.html http://www.postgis.us/downloads/post_gis_day_cards_letter.pdf http://www.postgis.us/downloads/post_gis_day_cards_a4.pdf If per chance you can't figure out how to put to good use any of these functions, you can always 1) Reference the PostGIS manuals: http://www.postgis.org/documentation/ and/or 2) our book http://www.postgis.us/page_buy_book Thanks, Regina and Leo ___ 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 Day
I'm planning on biting the bullet and learning about use the raster functionality with a beer in my hand... is that the sort of thing you were after? cheers Ben On 12/11/2011, at 12:21 PM, Paul Ramsey wrote: PostGIS day is coming up (November 17, the day after GIS day), any suggestions on ideas for how to mark this special day? P. ___ 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] missing proj4text in spatial_ref_sys causing dump restore problems.
G'day all, I'm having some problems with dumping / restoring a database, I think due to missing proj4text in the definitions. I'll preface this with a note that I don't want this particular projection, so I could just delete it, but the mechanism for the problem is intriguing me. the problems are in the srid=2046 - 2055 range Hartebeesthoek94. from sptatial_ref_sys.sql --- --- EPSG 2046 : Hartebeesthoek94 / Lo15 --- INSERT INTO spatial_ref_sys (srid,auth_name,auth_srid,srtext,proj4text) VALUES (2046,'EPSG',2046,'PROJCS[Hartebeesthoek94 / Lo15,GEOGCS[Hartebeesthoek94,DATUM[Hartebeesthoek94,SPHEROID[WGS 84,6378137,298.257223563,AUTHORITY[EPSG,7030]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY[EPSG,6148]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4148]],UNIT[metre,1,AUTHORITY[EPSG,9001]],PROJECTION[Transverse_Mercator_South_Orientated],PARAMETER[latitude_of_origin,0],PARAMETER[central_meridian,15],PARAMETER[scale_factor,1],PARAMETER[false_easting,0],PARAMETER[false_northing,0],AUTHORITY[EPSG,2046],AXIS[Y,WEST],AXIS[X,SOUTH]]',''); When I dump, I get : 2046EPSG2046PROJCS[Hartebeesthoek94 / Lo15,GEOGCS[Hartebeesthoek94,DATUM[Hartebeesthoek94,SPHEROID[WGS 84,6378137,298.257223563,AUTHORITY[EPSG,7030]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY[EPSG,6148]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4148]],UNIT[metre,1,AUTHORITY[EPSG,9001]],PROJECTION[Transverse_Mercator_South_Orientated],PARAMETER[latitude_of_origin,0],PARAMETER[central_meridian,15],PARAMETER[scale_factor,1],PARAMETER[false_easting,0],PARAMETER[false_northing,0],AUTHORITY[EPSG,2046],AXIS[Y,WEST],AXIS[X,SOUTH]] with no extra whitespace (ie tabs) at the end, so my attempt to load ends with _postgis.dump:15201: ERROR: missing data for column proj4text CONTEXT: COPY spatial_ref_sys, line 476: 2046 EPSG2046 PROJCS[Hartebeesthoek94 / Lo15,GEOGCS[Hartebeesthoek94,DATUM[Hartebeesthoek94,S... both the original database and the new one are in utf8, the file was dumped on my local machine and shared via subversion, but I'm still having the same problem on my local machine. I can get around it by deleting these before dumping, or maybe by using full inserts, but I'd rather not do that for size reasons - my questions are: why has the space been lost, and has this never happened to anyone else, and why does this group of projections not have proj4text? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Solved missing proj4text in spatial_ref_sys causing dump restore problems.
Mea Culpa, I was opening it in a text editor that was trimming whitespace from each line ending... (including tabs) .. before I committed it. Although I still don't understand why no proj4text? cheers Ben On 14/11/2011, at 1:35 PM, Ben Madin wrote: G'day all, I'm having some problems with dumping / restoring a database, I think due to missing proj4text in the definitions. I'll preface this with a note that I don't want this particular projection, so I could just delete it, but the mechanism for the problem is intriguing me. the problems are in the srid=2046 - 2055 range Hartebeesthoek94. from sptatial_ref_sys.sql --- --- EPSG 2046 : Hartebeesthoek94 / Lo15 --- INSERT INTO spatial_ref_sys (srid,auth_name,auth_srid,srtext,proj4text) VALUES (2046,'EPSG',2046,'PROJCS[Hartebeesthoek94 / Lo15,GEOGCS[Hartebeesthoek94,DATUM[Hartebeesthoek94,SPHEROID[WGS 84,6378137,298.257223563,AUTHORITY[EPSG,7030]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY[EPSG,6148]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4148]],UNIT[metre,1,AUTHORITY[EPSG,9001]],PROJECTION[Transverse_Mercator_South_Orientated],PARAMETER[latitude_of_origin,0],PARAMETER[central_meridian,15],PARAMETER[scale_factor,1],PARAMETER[false_easting,0],PARAMETER[false_northing,0],AUTHORITY[EPSG,2046],AXIS[Y,WEST],AXIS[X,SOUTH]]',''); When I dump, I get : 2046 EPSG2046PROJCS[Hartebeesthoek94 / Lo15,GEOGCS[Hartebeesthoek94,DATUM[Hartebeesthoek94,SPHEROID[WGS 84,6378137,298.257223563,AUTHORITY[EPSG,7030]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY[EPSG,6148]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4148]],UNIT[metre,1,AUTHORITY[EPSG,9001]],PROJECTION[Transverse_Mercator_South_Orientated],PARAMETER[latitude_of_origin,0],PARAMETER[central_meridian,15],PARAMETER[scale_factor,1],PARAMETER[false_easting,0],PARAMETER[false_northing,0],AUTHORITY[EPSG,2046],AXIS[Y,WEST],AXIS[X,SOUTH]] with no extra whitespace (ie tabs) at the end, so my attempt to load ends with _postgis.dump:15201: ERROR: missing data for column proj4text CONTEXT: COPY spatial_ref_sys, line 476: 2046 EPSG2046 PROJCS[Hartebeesthoek94 / Lo15,GEOGCS[Hartebeesthoek94,DATUM[Hartebeesthoek94,S... both the original database and the new one are in utf8, the file was dumped on my local machine and shared via subversion, but I'm still having the same problem on my local machine. I can get around it by deleting these before dumping, or maybe by using full inserts, but I'd rather not do that for size reasons - my questions are: why has the space been lost, and has this never happened to anyone else, and why does this group of projections not have proj4text? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] no row selected on spatial query
Puneet - I think this can happen if you load a shapefile that is projected into a table specifying a lat long srid. I don't think the definition restricts the range of units. You will have to change the column definition I guess. cheers Ben On 28/10/2011, at 8:06 AM, Puneet Kishor wrote: A brief follow-up on this -- On Oct 27, 2011, at 5:57 PM, Puneet Kishor wrote: I inherited two tables like so db=# select ST_Extent(the_geom) from t1; st_extent - BOX(-180 5.67656603969958,180 89.951703269) (1 row) db=# select ST_Extent(the_geom) from t2; st_extent --- BOX(-3666182.8927 1296299.9824,4440422.8742 8820425.3008) (1 row) I want to convert t2 to match t1. So, I tried the following -- db=# UPDATE t2 SET the_geom = ST_Transform(the_geom, 4326); UPDATE 1354 But that didn't do it. db=# select ST_Extent(the_geom) from t2; st_extent --- BOX(-3666182.8927 1296299.9824,4440422.8742 8820425.3008) (1 row) What do I do to transform t2 so its geometry matches t1? Turns out t2 was loaded from a shapefile using shp2pgsql using SRID 4326. Its table definition is like so CREATE TABLE t2 ( gid serial NOT NULL, objectid integer, type integer, the_geom geometry, CONSTRAINT t2_pkey PRIMARY KEY (gid ), CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326) ) WITH ( OIDS=FALSE ); So, in spite of the `enforce_srid_the_geom` CONSTRAINT which should restrict geometry to 4326, seems like meter values got inserted into the table. How is that possible? And, in any case, how can I repair this? -- Puneet Kishor ___ 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 you rebuild a multipolygon from rings?
Thanks strk,
but I'm still confused. If I don't select the .geom component, I can't
dumprings... when I do, I end up with 90 rows in a table of geometry dumps.
# select id, (the_geom).path, st_astext((the_geom).geom) from phl2 where id =
58;
id | path | st_astext
+--+--
58 | {57} | POLYGON((126.023966 8.568675,126.023895 8.56901,126.013809
8.570289,126.023912 8.56893,126.023966 8.568675))
(1 row)
# select id, ((the_geom).path), st_isvalidreason((the_geom).geom) from phl2
where st_isvalid((the_geom).geom) is false;
NOTICE: Self-intersection at or near point 126.024 8.56893
id | path | st_isvalidreason
+--+-
58 | {57} | Self-intersection[126.02391195391 8.56893000619979]
which is the polygon ring I want to delete
I can delete it, but I now want to insert this polygon (the remaining 89 rows)
back into my table of polgons (the dump from the first step), and then turn
them back into a multipolygon. In fact, I can do the last step. But I can't get
the geometry dump back into the polygons table in geometry dump format.
Maybe I'm trying to do this wrong, but there must be a function I haven't found?
cheers
Ben
On 21/10/2011, at 3:17 PM, Sandro Santilli wrote:
On Fri, Oct 21, 2011 at 01:33:43PM +0800, Ben Madin wrote:
But rebuilding the multipolygons is not so simple (for me) - I need to
re-aggregate the remaining rings into a geometry dump to reinsert into the
other dumped polygons to rebuild the multipolygon.
...
insert into phl (the_geom) select ((st_dump(the_geom))) from summ.ctybnda
where year=2005 and ccode like 'PHL';
...
insert into phl2 (the_geom) select st_dumprings(((the_geom).geom)) from phl
where id = 2601;
You're stripping out the path component of output from st_dump
and st_dumprings. Those are the keys to your later rebuilding.
--strk;
() Free GIS Flash consultant/developer
/\ http://strk.keybit.net/services.html
___
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] Can you rebuild a multipolygon from rings?
G'day all, We have a problem with erroneous geometries that we can't edit using QGIS - the geometries are too big, and the application just hangs. The geometries are multipolygons, and they contain invalid rings. To get at them using postgis seems to require two levels of dumping. First, st_dump to split the Multipolygons into polygons, then st_dumprings to get at the bad ring. But rebuilding the multipolygons is not so simple (for me) - I need to re-aggregate the remaining rings into a geometry dump to reinsert into the other dumped polygons to rebuild the multipolygon. I'm not sure how to express this more articulately. sorry. I have done : create table phl(id serial unique, country varchar not null default 'tmp', the_geom geometry_dump); insert into phl (the_geom) select ((st_dump(the_geom))) from summ.ctybnda where year=2005 and ccode like 'PHL'; Then I find the bad geometry : select id, ((the_geom).path), st_isvalid((the_geom).geom) from phl where st_isvalid((the_geom).geom) is false; create table phl2(id serial unique, country varchar not null default 'tmp', the_geom geometry_dump); insert into phl2 (the_geom) select st_dumprings(((the_geom).geom)) from phl where id = 2601; select id, ((the_geom).path), st_isvalid((the_geom).geom) from phl2 where st_isvalid((the_geom).geom) is false; And Delete it delete from phl2 where id = 58; Now is where I'm stuck - I need to congeal the remaining rings back into the first table, replacing the polygon that was unhealthy, but I have a geometry_dump, and need to aggregate it into another geometry dump to fit into the table... I can happily aggregate geometry dumps into geometries, but I don't seem to be able to collect my rings back into a geometry dump. This works to rebuild the original multipolygon from the dumped polygons table : update summ.ctybnda c set the_geom = (select st_multi(st_collect(n.the_geom)) from (select country, ((p.the_geom).geom) as the_geom from phl p) as n) where c.year = 2005 and c.ccode = 'PHL'; but I can't get my repaired polygon back into the dumped polygons table. Any idea? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Need help : Getting Started to PostGIS
Wendi Check the book at this site. http://www.manning.com/obe/ or google postgisonline cheers Ben On 30/09/2011, at 2:08 PM, Wendi Adrian wrote: Hi everyone, I am newbie in using PostGIS database and want to build GIS database using PostGIS and PostgreSQL. Can anyone help me what step to build GIS database using PostGIS? I already downloaded installer. Please help me, Regards, Wendi ___ 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] schemas and postgis data
Robert, You can get as complex as you like, but one great use of schemas (I think) has been to manage backing up data - especially over the internet, another is to manage user access at a more granular level. if you put your static data (ie background maps) into one schema, then when dumping you can use the -N flag to avoid dumping that schema. By doing this we avoid backing up several hundred megabytes every night, but new research location data is backed up daily - only a few megabytes similarly, for some projects you might have data on users and access controls, but when analysing the data this isn't necessary, so putting important (perhaps research) data into it's own schema makes it easy to export from a database server to a local machine for analysis. Dont forget to ALTER DATABASE mydatabase SET search_path TO mymainschema, myotherschema, someotherstuff, public; cheers Ben On 22/09/2011, at 6:17 PM, Robert Buckley wrote: I have just read this explaining about how to structure data and functions within postgresql http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html ...The public schema is where the PostGIS functions and system tables get installed, so if you dump that schema you get all those definitions in your dump. If those definitions are mixed in amongst your data, loading them into a fresh database gets tricky: are the paths to the libraries the same? are there function name clashes? (The utils/postgis_restore.pl script attempts manfully to strip out PostGIS components from a dump file to allow a clean restore, but it is hard to get 100% performance.) If, on the other hand, all your data is neatly separated into its own schema, you can neatly backup just that schema and avoid having PostGIS system information mixed in with your data. That means you can easily restore your data into any version of PostGIS and PostgreSQL that you like. So upgrades are easy easy easy. Remember: Store your data in a schema other than public. Basically Paul recommends saving geodata in a different schema to the postgis functions. My questions are... 1 if the data is located in a different schema which does not have the 800 odd postgis functions, are the functions still available to the data? 2 are cross schema queries allowed? 3 does it also make sense to seperate non-spatial tables into their own schemas? thanks for any advice, Rob ___ 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 table doesn't show up in QGIS
Puneet,
Not uncommon problem - QGIS looks in the Geometry_columns table - you might
want to check that the geometry column is registered in this table. If not you
can run :
SELECT Populate_Geometry_Columns(); -- for all tables
or
SELECT Populate_Geometry_Columns('public.myspatial_table'::regclass); -- for
myspatial_table
http://postgis.refractions.net/documentation/manual-1.5/Populate_Geometry_Columns.html
Note that using AddGeometryColumns() does this, but it can be corrupted.
Importing tables doesn't do this automagically.
cheers
Ben
On 15/09/2011, at 10:07 AM, Puneet Kishor wrote:
This happens from time to time... I have a table like so
CREATE TABLE foo
(
gid serial NOT NULL,
the_geom geometry,
CONSTRAINT foo_pk PRIMARY KEY (gid ),
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
WITH (
OIDS=FALSE
);
ALTER TABLE foo
OWNER TO postgres;
CREATE INDEX foo_geom_idx
ON states
USING gist
(the_geom );
Yet, the above table doesn't show up in QGIS open dialog. If I check the box
to show tables with no geometry, then the above table shows up as not having
any geometry. The table has 20K+ rows, and while a few rows have invalid
geometry, for the most part, the table has good data in it.
What is the reason for the above?
Puneet.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with convex hulls that cross the dateline
Sarah,
I'm sure that there are good reasons not to do this, but could you translate
your points left or right, create your convex hull then translate the polygon
back...?
Otherwise you could project it onto a custom projection that covers your area
of interest? As an example look for an equal area proj string (I know there is
one for Australia GDA94 Albers - http://www.spatialreference.org/ref/epsg/3577)
and shift the lat and lon parameters...
cheers
Ben
On 07/09/2011, at 1:13 AM, Sarah Berke wrote:
Hello,
I'm having trouble making convex hulls that cross the international dateline.
Imagine 4 points making a square with the international dateline running down
the middle--I would like the convex hull to be that square, but instead I get
a giant rectangle that goes across the entire planet. I've done some reading,
and it sounds like GEOS generally has a hard time dealing with the
dateline--is that accurate? Does anyone know of a way to get around this? I
was thinking that it might work if I use an SRID that is just like 4326 but
with a central meridian of 180, does that sound like a good plan? I'm pretty
new to postGIS and I'm not sure how to either find such an SRID or how to
define it--I've been trying to find an explanation of SRID syntax and so far
coming up empty. If anyone has advice for solving this problem, or for places
where I can learn more about defining custom SRIDs, I'd be really grateful!
Here's an example--if you make this table and then look at it in QGIS (or
whatever) along with a world map, you'll see a big rectangle spanning the
entire map. On a map with central meridian of zero, I'd want to see half the
polygon on the left side of the map and the other half on the right side.
CREATE TABLE example AS
SELECT ST_ConvexHull(
ST_Collect(ST_GeomFromText('MULTIPOINT(175 5, 175 30, -175 5, -175 30)')
))::geography(Polygon, 4326) ;
Thanks very much,
Sarah
_
Sarah K Berke
Postdoctoral Scholar
Department of the Geophysical Sciences
University of Chicago
5734 S. Ellis Ave
Chicago, IL 60637
___
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] OT Understanding slow queries
I'm no expert at this, but my understanding (which is limited) was that you are asking for the whole table, so indexing doesn't really get used (my understanding is that indexing helps to find the page for a subset of data more quickly than scanning through the whole lot). Also, you might be able to get some speed up by using a different join type (outer join and not null where clause)? cheers Ben On 25/08/2011, at 9:41 PM, Charles Galpin wrote: If this is too off topic, please let me know and I'll sign up on a postgres list to get help. But this is related to my use of postgis and If anyone knows this stuff, it's you guys. I have an example query that I expect to be much faster, but my main concern is we are about to do some visualization of historical congestion data which will require queries across much larger data sets - like 150 million records a day. We are about to test using partitions but the number per table will still be much larger than what I am dealing with now. So here is a query I would think would be much faster than 43 seconds for two tables, one with about 97k rows, and the other 3.2 million. explain select count(l.*) from links l, source_link ld where l.link_id = ld.link_id; /* 'Aggregate (cost=174731.72..174731.73 rows=1 width=32)' ' - Hash Join (cost=13024.27..166784.14 rows=3179029 width=32)' 'Hash Cond: (ld.link_id = l.link_id)' '- Seq Scan on source_link ld (cost=0.00..58282.29 rows=3179029 width=10)' '- Hash (cost=10963.12..10963.12 rows=96812 width=42)' ' - Seq Scan on links l (cost=0.00..10963.12 rows=96812 width=42)' */ Each table has an index on link_id, defined like this CREATE INDEX links_link_id_idx ON links USING btree (link_id); CREATE INDEX source_link_link_id_idx ON source_link USING btree (link_id); Shouldn't this index prevent these sequential scans, or am I misreading this? Should this really take 43 seconds? thanks for any advice, charles ___ 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] OT Understanding slow queries
Steve, does this just apply to count(*), or is count(id) just as bad? I was originally a MySQL user and count(*) could be very efficient there. cheers Ben On 26/08/2011, at 12:01 AM, Stephen Woodbridge wrote: The issue here is the count(*) which forces a full table scan in postgresql as fork mentioned. You need to look at a real query, unless you are really doing a count(*). -Steve On 8/25/2011 11:49 AM, Ben Madin wrote: I'm no expert at this, but my understanding (which is limited) was that you are asking for the whole table, so indexing doesn't really get used (my understanding is that indexing helps to find the page for a subset of data more quickly than scanning through the whole lot). Also, you might be able to get some speed up by using a different join type (outer join and not null where clause)? cheers Ben On 25/08/2011, at 9:41 PM, Charles Galpin wrote: If this is too off topic, please let me know and I'll sign up on a postgres list to get help. But this is related to my use of postgis and If anyone knows this stuff, it's you guys. I have an example query that I expect to be much faster, but my main concern is we are about to do some visualization of historical congestion data which will require queries across much larger data sets - like 150 million records a day. We are about to test using partitions but the number per table will still be much larger than what I am dealing with now. So here is a query I would think would be much faster than 43 seconds for two tables, one with about 97k rows, and the other 3.2 million. explain select count(l.*) from links l, source_link ld where l.link_id = ld.link_id; /* 'Aggregate (cost=174731.72..174731.73 rows=1 width=32)' ' - Hash Join (cost=13024.27..166784.14 rows=3179029 width=32)' 'Hash Cond: (ld.link_id = l.link_id)' '- Seq Scan on source_link ld (cost=0.00..58282.29 rows=3179029 width=10)' '- Hash (cost=10963.12..10963.12 rows=96812 width=42)' ' - Seq Scan on links l (cost=0.00..10963.12 rows=96812 width=42)' */ Each table has an index on link_id, defined like this CREATE INDEX links_link_id_idx ON links USING btree (link_id); CREATE INDEX source_link_link_id_idx ON source_link USING btree (link_id); Shouldn't this index prevent these sequential scans, or am I misreading this? Should this really take 43 seconds? thanks for any advice, charles ___ 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] ERROR: geometry contains non-closed rings
G'day Puneet, Maybe you could start with : SELECT gid, st_isvalidreason(the_geom), st_summary(the_geom) FROM data WHERE st_isclosed(the_geom) is FALSE; cheers Ben On 19/08/2011, at 12:19 AM, Mr. Puneet Kishor wrote: I loaded a bunch of data (that came from ArcMap) into PostGIS (1.5.3) via shp2pgsql and am trying to draw it with MapServer 6.0.1. I get msDrawMap(): Image handling error. Failed to draw layer named 'foo'. msPostGISLayerWhichShapes(): Query error. Error (ERROR: geometry contains non-closed rings ) Many thanks in advance for your suggestions. -- Puneet Kishor ___ 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] Fixing linebreaks
G'day all, I have 151 000 roads stored as multilinestrings (in EPSG 4326), and trying to find the start and end points I have discovered that nearly every one has approximately 5 - 15 mm separating the ends, so st_linemerge is not working for me. I have considered st_snaptogrid, but am I missing something more simple... cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] connecting to postgis form mapserver
Vishal, David has probably nailed this one, but if not or otherwise you might also want to put DEBUG ON into your map and the LAYER definitions (you need both) - the debug output from postgis connections is comprehensive if a little complex initially, but very useful for learning. It doesn't hurt to explicitly set projections as well, to make sure they match. cheers Ben On 18/08/2011, at 4:09 AM, Vishal Mehta wrote: Hi all, I am testing out mapserver-postgis and a simple test is returning an empty map. Here is my map file MAP IMAGETYPE PNG EXTENT 77.5 12.95 77.7 13.01 SIZE550 450 IMAGECOLOR 255 255 255 SHAPEPATH ../data # Start of LAYER DEFINITIONS--- LAYER CONNECTIONTYPE POSTGIS NAME wards # Connect to a remote spatial database CONNECTION host=localhost port=5432 dbname=empty user=postgres password=xxx # Get the lines from the 'geom' column of the 'wards' table DATA 'geom from myschema.wards198 using srid=4326 using unique gid' STATUS ON TYPE POLYGON CLASS NAME wards STYLE OUTLINECOLOR 255 0 0 END END END # End of LAYER DEFINITIONS --- END --- And the html that calls it -- html head titlemy postgis map/title /head body bgcolor=white center br table width=570 tr td br img width=550 height=450 src=/cgi-bin/mapserv.exe?map=C:\OSGeo4W/apps/mapserver-tutorial/mytests/example.mapmode=map border=1 /td /tr /table br /body /html Other info: Platform: - Windows 7, postgresql9 , postgis 2.0 - I installed Mapserver separately through the OSGEO4 installer. - I can connect to the same database through php - I can create simple mapserver apps using shapefiles etc alright (e.g. same map file above, calling a shapefile, works fine) - Mapserv.exe –v shows that it was installed with postgis support. - Also can Mapserver handle postgis Mulipolygon geometry ok? I did check most of the points here which seemed ok ( i did not check those in this list that I don’t really know how to in Windows ). http://trac.osgeo.org/mapserver/wiki/PostGIS I realize this might be a mapserver mailing list qn, but thought that some of you might know the answer…it might be a simple syntax problem that I’ve missed… Thanks, Vishal ___ 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] Help dumping data from an old database
Chris, This is probably a bad suggestion, but iff you are desparate and iff you can build a more recent postgis, then to dump it out you may find that creating a simlink from a more recent liblwgeom.so with the name of the missing one might work. the COPY command won't actually take anything other than the SQL, so shouldn't affect the importing. cheers Ben On 17/08/2011, at 6:28 AM, Chris Gat wrote: Hello, I've recently been tasked with reviving an older database that hasn't been touch in a while. Both postgres and postgis were used to create this database. My ultimate goal at this point is to be able to dump the database from the linux server where it currently resides, to a mac workstation. Here is the short version of what I've done so far and the problem I'm having: build/install postgresql 8.2.3 at this point, I can access the database (via psql, SELECT), but if I try to use pg_dump (pg_dump MYDB MYDB.sql), I get the error: ERROR: could not access file $libdir/liblwgeom.1.2.so: No such file or directory STATEMENT: COPY public.boundaries (gid, fnode_, tnode_, lpoly_, rpoly_, length, world_, world_id, bnd_type, bnd_status, bnd_study, area, len, the_geom) TO stdout; Obviously, after some investigation, I realize I need postgis. So I, build/install proj4 4.7 build/install geos 3.1.1 when I try to build postgis-1.2.1, I get the build error (after running make): lwgeom_geos_c.c: In function `postgis_geos_version': lwgeom_geos_c.c:84: warning: implicit declaration of function `VARATT_SIZEP' lwgeom_geos_c.c:84: error: invalid lvalue in assignment lwgeom_geos_c.c: In function `relate_full': lwgeom_geos_c.c:2073: error: invalid lvalue in assignment lwgeom_geos_c.c: In function `GEOS2LWGEOM': lwgeom_geos_c.c:2430: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2439: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2449: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2450: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2454: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2455: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2476: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c: In function `polygonize_garray': lwgeom_geos_c.c:2815: warning: passing arg 1 of `GEOSPolygonize' from incompatible pointer type lwgeom_geos_c.c: In function `LWGEOM_buildarea': lwgeom_geos_c.c:2965: warning: passing arg 1 of `GEOSPolygonize' from incompatible pointer type lwgeom_geos_c.c:3008: warning: assignment discards qualifiers from pointer target type I can build/install postgis-1.3 and postgis-1.4, but these versions don't create the proper liblwgeom.so file. Any ideas as to why this is occurring would help greatly. I should also mention that the most important information in this database has nothing to do with postgis, therefore, if there is a way to ignore the tables associated with postgis, and thus avoid the liblwgeom requirement, that would suffice. Some additional information: -when configuring postgres, proj4, geos, I used --prefix=/mydirectory/ -when configuring postgis, I used --prefix=/mydirectory/ --with-psql=/pathToPgconfig --with-geos=/pathToGeoConfig --with-proj=/mydirectory/ Also, I use GNU make 3.8 Thanks for you help, I appreciate it. Chris ___ 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] Difficulty importing example file nyc_buildings.sql
Bruce,
On 15/08/2011, at 6:28 PM, b...@brucecallander.com wrote:
$ /usr/local/pgsql-9.0/bin/psql -f /Users/bacmac/nyc_buildings.sql nyc
The Geoserver instructions are not explicit about where to put the
nyc_buildings.sql file so I have left it in my own user directory 'bacmac'.
It doesn't matter, as long as the -f part of the command describes it as you
have. You may find it convenient to keep all these scripts together in a single
directory somewhere while you are starting.
Execution of the command produces a string of errors beginning with:
CREATE TABLE
psql:/Users/bacmac/nyc_buildings.sql:4: ERROR: function
addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does
not exist
LINE 1: SELECT AddGeometryColumn('','nyc_buildings','the_geom','2908...
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
psql:/Users/bacmac/nyc_buildings.sql:5: ERROR: current transaction is
aborted, commands ignored until end of transaction block
psql:/Users/bacmac/nyc_buildings.sql:6: ERROR: current transaction is
aborted ... and so on.
I realise that part of my difficulties stem from a lack of conceptual
awareness of how the various parts of PostGIS fit together (databases, data
stores, tables, clusters...) and what directory structure the PostGIS
installation creates. I am very familiar with Access databases but that may
be dangerous because the basic paradigm for PostGIS may be different. Are
there any diagrams out there that give a basic conceptual view of Geoserver
and PostIGIS? Also, I am coming at this from an enterprise SDI policy and
implementation end, not from an IT/Unix background (probably very obvious!).
Trying to construct a coherent overall picture based on the predominantly
text-based, IT-heavy advice online is proving a challenge.
One of the neat / good / bad / helpful / troublesome things that you can do in
postgres is define two functions with the same name, as long as they have
different parameters and or parameter types. In this case, the import script is
loading your sql, but has inappropriately enclosed the srid in quotes '2908'
the function you want is:
text AddGeometryColumn(varchar schema_name, varchar table_name, varchar
column_name, integer srid, varchar type, integer dimension);
which specify that the srid should be integer. Because your call starts like :
SELECT AddGeometryColumn('','nyc_buildings','the_geom','2908...
it won't match one of the above calls (unknown can go into varchar columns, but
not into integer columns, which must be integer.)
The quickest fix might be to edit the nyc.sql file and remove the quotes from
around the srid part of the addgeometrycolumn call.
cheers
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS documentation license
I kind of agree with Regina here... On 28/07/2011, at 1:50 AM, Paragon Corporation wrote: They're willingness to give back these changes wouldn't have been any different if PostGIS were GPL or BSD because to them PostGIS is just a wheel in their armor like any other database software would be. However the fact that PostGIS is GPL does give some a pause for concern as to how they distribute it etc and their willingness to even use it since it does bring up the question of where their software begins and PostGIS ends. We certainly don't have the ability to give much back - except the bug reports and maybe some help on lists, but we do occasionally have clients (some of them very small local parts of very large multinational organisations) who want solutions without any of the complications of the licensing required by GPL. If it looks like getting confusing or complicated, they would rather just pay. While I personally might believe that this is misguided, I can't really afford to turn them away... although if they could give me the money they shelled out on proprietary database licences, I wouldn't have so many problems... Anyhow... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] exterior rings in multipolygons
Maybe you could share the polygon - does it pass tests like st_isvalid()? cheers Ben On 27/07/2011, at 4:16 AM, Mr. Puneet Kishor wrote: On Jul 26, 2011, at 9:56 AM, Sandro Santilli wrote: On Tue, Jul 26, 2011 at 09:47:23AM -0500, Mr. Puneet Kishor wrote: On Jul 26, 2011, at 9:34 AM, Sandro Santilli wrote: On Tue, Jul 26, 2011 at 09:31:50AM -0500, Mr. Puneet Kishor wrote: ERROR: function st_interiorringn(geometry) does not exist Check your parens, the call above is missing a parameter (ring number). That is a bit embarrassing. Yes, I had missed out on giving the ring number. However, still no luck -- SELECT objectid, ST_NRings(the_geom) num_of_rings, ST_NumInteriorRings(the_geom) num_of_int_rings, ST_NumPoints(ST_ExteriorRing(ST_GeometryN(the_geom,1))) num_of_points_ext_ring, ST_AsText(the_geom) wkt_feature, ST_AsText(ST_MakePolygon(ST_ExteriorRing(ST_GeometryN(the_geom, 1 wkt_ext_ring, ST_AsText(ST_MakePolygon(ST_InteriorRingN(ST_GeometryN(the_geom, 2), 2))) wkt_int_ring_a, ST_AsText(ST_MakePolygon(ST_GeometryN(the_geom, 2))) wkt_int_ring_b FROM table WHERE objectid = 280; objectid: 280; num_of_rings: 4; num_of_int_rings: 3; num_of_points_ext_ring: 496; wkt_feature: MULTIPOLYGON(((1),(2),(3),(4)); wkt_ext_ring: POLYGON((1)); wkt_int_ring_a: ; wkt_int_ring_b: As you see, I tried to get the interior ring n two different ways. Not getting anywhere. Write down a matrix of all combinations you tried. Use real paper, and pencil. Publish a scanned version. So, as advised, I got a real pencil and paper, drew out all my options, scanned them in, and discovered that I had a poor understanding on the relationship of MULTIPOLYGONs and GEOMETRIES. Turns out, my MULTIPOLYGON has 1 GEOMETRY, 4 rings, one being exterior and three interior rings. Hence, an expression like the following works well ST_NumPoints(ST_InteriorRingN(ST_GeometryN(the_geom, 1), 2)) Now, here is where it gets interesting. My test feature in reality is a lot more complicated, as can be seen in the attached image (see the blue bordered multipoly). I took the shapefile and shp2pgsql-ed it, and now I get only 4 rings in my query. My guess is that these four rings are the ones on the top of the image. So, I thought maybe I have another GEOMETRY that I am missing out on. But, no... ST_NumGeometries(the_geom) = 1. Can anyone theorize what happened to the rest of the feature? How did it get lost in translation? Was it simply ignored, or was it made into a different feature? Or, is it there, but I still haven't understood the MULTIPOLYGON/GEOMETRY/POLYGON/LINERSTRING nexus really well. 280.png___ 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: ptarray_area_spheroid: cannot handle ptarray that crosses equator
G'day Sarah
I'm afraid I'm no expert on geography types, but yes, in some cases st_area can
process invalid geometries (like you have described), but they are after all
invalid geometries, so it returns 0 . For example, looking at your second
example :
=# SELECT ST_AREA(the_geom), ST_ISVALID(the_geom), ST_ISVALIDREASON(the_geom),
ST_SUMMARY(the_geom) FROM (SELECT ST_GEOMFROMTEXT('POLYGON((15 -5, 16 0, 15 0,
16 -2, 15 -5))') the_geom) as foo;
NOTICE: Self-intersection at or near point 15.7143 -1.42857
st_area | st_isvalid | st_isvalidreason|
st_summary
-++---+-
0 | f | Self-intersection[15.7142857142857 -1.42857142857143] |
+
|| |
Polygon[B] with 1 rings+
|| |
ring 0 has 5 points +
|| |
(1 row)
it is not because one side rules is the inverse of the other that the area is
0, it just returns 0 not an error. My guess is that the invalid geography is
causing an uncaught error, but there is no st_isvalid(geography), hence using
it on geometry type. This would be my approach to finding the invalid
geometries. How you repair them...? I guess it would depend on how they were
originally collected, how much each one cost to collect or what they meant.
cheers
Ben
On 25/07/2011, at 5:27 AM, Sarah Berke wrote:
I've figured out a little more detail about my ST_Area problem, perhaps this
might help someone give me some advice for troubleshooting. To provide a
little more background, I've generated several thousand polygons using
ST_ConvexHull from collections of points (I've tried both
ST_ConvexHull(ST_Collect(points)) and ST_ConvexHull(ST_Union(points)) ) and
when I try to calcuate their areas I get this error:
ERROR: ptarray_area_spheroid: cannot handle ptarray that crosses equator
CONTEXT: SQL function st_area statement 1
The problem seems to be that ST_Area cannot handle some polygons with
internal crosses (i.e. bowtie-shaped) that share a border with the equator,
even though it can handle them elsewhere. For example:
SELECT ST_AREA(ST_GEOGFROMTEXT('POLYGON((15 -5, 15 5, 16 0, 16 -5, 15 -5))'))
;
works just fine--this is just a simple polygon that crosses the equator. By
the same token, a simple polygon that borders the equator works:
SELECT ST_AREA(ST_GEOGFROMTEXT('POLYGON((15 -5, 15 0, 16 0, 16 -5, 15 -5))'))
;
Switching the 2nd and 3rd coordinates, however, makes a bowtie-shaped polygon
which is valid, but it shares a border with the equator and ST_Area cannot
take it:
SELECT ST_AREA(ST_GEOGFROMTEXT('POLYGON((15 -5, 16 0, 15 0, 16 -5, 15 -5))'))
;
ERROR: ptarray_area_spheroid: cannot handle ptarray that crosses equator
CONTEXT: SQL function st_area statement 1
The same kind of formation works fine, however, if it does not share a border
with the equator :
SELECT ST_AREA(ST_GEOGFROMTEXT('POLYGON((15 -5, 16 1, 15 0, 16 -5, 15 -5))'))
;
SELECT ST_AREA(ST_GEOGFROMTEXT('POLYGON((15 -4, 16 1, 15 1, 16 -4, 15 -4))'))
;
Again, here are my system details:
PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
POSTGIS=1.5.2 GEOS=3.3.0-CAPI-1.7.0 PROJ=Rel. 4.7.1, 23 September
2009 LIBXML=2.7.6 USE_STATS
I assume that one or more of the polygons I made with ConvexHull is a
bowtie-shape sharing a border with the equator, but why would ST_ConvexHull
generate a shape like that? How can I avoid this problem? Short of visually
examining the map of every one of these several thousand polygons, how do I
even figure out which one is the culprit? Is there a better way to go from
points or multipoints to polygons?
Any insight would be really great! Thanks!
_
Sarah K Berke
Postdoctoral Scholar
Department of the Geophysical Sciences
University of Chicago
5734 S. Ellis Ave
Chicago, IL 60637
Date: Fri, 22 Jul 2011 14:39:52 -0500
From: Sarah Berke skbe...@uchicago.edu
Subject: [postgis-users] ERROR: ptarray_area_spheroid: cannot handle
ptarraythat crosses equator
To: postgis-users@postgis.refractions.net
Message-ID:
cajupzdeptykhb4log1szea0msprgikpfhm70fh_atbbuxso...@mail.gmail.com
Content-Type: text/plain; charset=iso-8859-1
Hello,
I found a short thread on this subject from February 2011--I am now having
the same problem that Marcello had, and I wonder if anyone has found a fix
or workaround for this problem? Using the same example code that Marcello
provided:
SELECT 161109 polygon ,ST_AREA(ST_GEOGFROMTEXT('POLYGON((15.8327132977611
0.25,16 0.25,16 0,15.8566682147637
[postgis-users] Shapefiles exported from PostGIS and ESRI ArcView 9 compatibility
G'dat all, We have a client (potential client) who wants some mapping done and the maps returned in shapefile format. In the contract they insist on specifying that the shapefiles are compatible with ESRI's ArcView version 9 (their words, not mine). I don't have ESRI's ArcView version 9, and don't intend on spending that sort of money (more than the contract is worth). However, I'm sure that if what we send back doesn't open in ESRI's ArcView version 9 it will become our problem. Does anyone have any reason to suspect that a shapefile created using valid OGC geometry in PostGIS and exported using pgsql2shp would not or might not work on ESRI's ArcView version 9? Are there any issues that someone who is across platforms can help me with? (I routinely use shapefiles created using valid OGC geometry in PostGIS and exported using pgsql2shp in QGIS and MapServer and send them to other people, so I have no reason to be concerned except that the client insists on this line remaining the contract!) cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_area returning wrong results (and stretched polygon in the screen)
Hi Nicolas, I thought ST_Area should work with Geography types and return metres? Is this not so? cheers Ben On 21/07/2011, at 9:34 PM, Nicolas Ribot wrote: On 21 July 2011 14:42, p valdes p.valdes...@gmail.com wrote: Hi, I'm trying to calculate the area of a polygon, with strange results... First a description of the problem, the ugly details at the end of the post I have measured the perimeter of a pond. An irregular polygon vaguely 'chop shaped' without holes or spikes and closed (beginning and ending in the same point). For simplification purposes let's suppose that I was expecting something like the left shape... well, I'm obtaining instead this: expectedobtained ------ | | | | | | | | | |__| | | | | | ---| | | | | | | |_ - The right shape is not right. The polygon is clearly stretched in the vertical North-South axis in the screen. The satellite google map support that the real shape of the pond is the first represented and I think the same... Well I could live with this, but there's two more nasty consequences. 1) If I take a point inside or related to the polygon in a separated map layer the point is drawn OUTSIDE and far away below the figure of the pond (note also that the pond is printed much more big than expect, because the relative distance between a and b is the same in both, and seems right). Graphically: expected obtained --*a --- | || | | || | | || | | || | | || | | || | | || | | |_ | |_ -*b -- *a *b Maybe a problem of the map viewer software, yes but the second question is more serious: ST_distance calculates a distance between the a and b points of about 160 m for this rectangle. A max length of 160 m for this pond seems reasonable to me. but ST_area calculates an area for this polygon of almost 7 ha!, for a polygon that could fit in a rectangle of about 160x50 m!. I'm obtaining a faked area about seven times BIGGER than real, uh-oh... Its clear to me that I'm doing a very obvious and stupid mistake. I greatly appreciated if you could take a look at the problem or suggest any possible source of the error Thanks in advance pvaldes. . Details .. The points were registered with a garmin GPS as coordinates in degrees (ie: 43.254 N -4.839 W) I'm using PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.6.real (Debian 4.6.0-6) 4.6.1 20110428 (prerelease), 32-bit (obtained as Debian package from an official repository) postgis-1.5.3 (downloaded and compiled from the tarfile at postgis.refractions.net) This is the table CREATE TABLE mytable( ref serial PRIMARY KEY, name varchar(80), perimeter geography(POLYGON,4326) ); The polygon with lat/lon coords was loaded with something like: INSERT INTO mytable (perimeter, name) VALUES ( ST_GeogFromText( 'POLYGON(( -5.3850 43.230, -5.3858 43.231, -5.3859 43.245, ... -5.3850 43.230 ))', 4326),'myname'); And this is the very simple query returning strange results SELECT st_area(mytable.perimeter)::double precision AS square_meters FROM mytable WHERE name = 'myname'; I use pgsql2shp for obtaining a shapefile that I'm loading with thuban Hi, st_area works with data units, degrees in your case. So you are computing square degrees, which has no geographic meaning. You should project your data to a metric system and then perform area computation on the projected data. North of Spain corresponds to UTM 30N (SRID 32630). Nicolas ___ 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 returning wrong results (and stretched polygon in the screen)
p Nicely documented example - but can you provide the full geography of the dam (that's Australian for pond!) maybe using st_astext() so other's can try on their local machines? cheers Ben On 21/07/2011, at 8:42 PM, p valdes wrote: Hi, I'm trying to calculate the area of a polygon, with strange results... First a description of the problem, the ugly details at the end of the post I have measured the perimeter of a pond. An irregular polygon vaguely 'chop shaped' without holes or spikes and closed (beginning and ending in the same point). For simplification purposes let's suppose that I was expecting something like the left shape... well, I'm obtaining instead this: expectedobtained ------ | | | | | | | | | |__| | | | | | ---| | | | | | | |_ - The right shape is not right. The polygon is clearly stretched in the vertical North-South axis in the screen. The satellite google map support that the real shape of the pond is the first represented and I think the same... Well I could live with this, but there's two more nasty consequences. 1) If I take a point inside or related to the polygon in a separated map layer the point is drawn OUTSIDE and far away below the figure of the pond (note also that the pond is printed much more big than expect, because the relative distance between a and b is the same in both, and seems right). Graphically: expected obtained --*a --- | || | | || | | || | | || | | || | | || | | || | | |_ | |_ -*b -- *a *b Maybe a problem of the map viewer software, yes but the second question is more serious: ST_distance calculates a distance between the a and b points of about 160 m for this rectangle. A max length of 160 m for this pond seems reasonable to me. but ST_area calculates an area for this polygon of almost 7 ha!, for a polygon that could fit in a rectangle of about 160x50 m!. I'm obtaining a faked area about seven times BIGGER than real, uh-oh... Its clear to me that I'm doing a very obvious and stupid mistake. I greatly appreciated if you could take a look at the problem or suggest any possible source of the error Thanks in advance pvaldes. . Details .. The points were registered with a garmin GPS as coordinates in degrees (ie: 43.254 N -4.839 W) I'm using PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.6.real (Debian 4.6.0-6) 4.6.1 20110428 (prerelease), 32-bit (obtained as Debian package from an official repository) postgis-1.5.3 (downloaded and compiled from the tarfile at postgis.refractions.net) This is the table CREATE TABLE mytable( ref serial PRIMARY KEY, name varchar(80), perimeter geography(POLYGON,4326) ); The polygon with lat/lon coords was loaded with something like: INSERT INTO mytable (perimeter, name) VALUES ( ST_GeogFromText( 'POLYGON(( -5.3850 43.230, -5.3858 43.231, -5.3859 43.245, ... -5.3850 43.230 ))', 4326),'myname'); And this is the very simple query returning strange results SELECT st_area(mytable.perimeter)::double precision AS square_meters FROM mytable WHERE name = 'myname'; I use pgsql2shp for obtaining a shapefile that I'm loading with thuban ___ 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 returning wrong results (and stretched polygon in the screen)
pvaldes On 21/07/2011, at 10:41 PM, p valdes wrote: Thanks Ben, I'm trying to attack the problem with the geometry approach. I will add your correction just now Not a correction, but your previous post didn't include the full list of points you were using to construct the polygon... so no one can test it to see if there is a problem. If you send the full list of points (or export the polygon so it can be imported) others can check to see where the problem might be. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] AsText returns nothing but geometries exist in table
In lieu of an informed answer, logic would suggest the geometry must be in the table (if it is displaying correctly in a GIS application). Therefore, I would suggest that there may be some aspect of displaying it other than in a GIS application that is the problem. Are you using pgAdmin to view the table data? What operating system? There is a character limit - the normal limit on postgres rows is 2048 characters. But geometry data are not actually stored in the table, rather they are stored in a TOAST table. I would suggest that you try looking at it in psql (the command line application) or I believe there is a plug-in for pgAdmin that might help display the data there. Be aware also that if your geometries are large, the rows will be wide, and so displaying them in psql might be somewhat frustrating as you will have a large amount of blank space in the table heading etc. cheers Ben On 18/07/2011, at 8:30 PM, Phil James wrote: I have loaded some geometry into a postgis table but although the geometry is there (displays in Quantum and can query with ST_various correctly) it does not appear with AsText() or in the Edit Table view. The type is MultiPolygon using postgres 9 Postgis 1.5. Is there some character limit it exceeds? If so why does the the_geom column appear blank in Edit Table using PGADMIN as well? I have checked the geometry and it is valid. Thanks Phil ___ 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] converting to lat long
FWIW - PostGIS uses long lat format... If you are confident about the data consistency, this should be a pretty straightforward task - if you are not sure, it might still be quite simple if you are prepared to look into the mystical world of regular expressions. PostgreSQL offers support for these on the previously recommended string expressions page, but you will also need to look at the pattern matching page. If you haven't ever looked at regular expressions, there are a number of very good resources available on the web. They are annoyingly useful, so worth investing some time in learning about them. cheers Ben On 12/07/2011, at 8:58 PM, Yamini Singh wrote: Hi All, I have a column 'geocode' in a table which has attributes like 2329/4727 now I would like to convert these attributes in another column to 'lat' and 'long'. For example, 'Lat' column will have attribute '23.29' and 'long' column will have attribute '47.27'. Is there a way through which this can be converted automatically by a query or so. Looking fwd.. Thanks YJ ___ 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] Coordinate format for SRID:4326
Michael, On a computer, use decimal degrees. x axis first (abscissa - Eastings or Longitude), y axis second (ordinate - Northings or Latitude). In the cockpit, you may find yourself using sexagesimal degrees, and you may hear people quote 'Lat-lon', but if you look at the def's below, you will see +proj=longlat just my opinion... cheers Ben On 11/07/2011, at 10:40 PM, Gheorghiu, Mihai wrote: I am confused by the references to sexagesimal degrees that I found on this list and elsewhere on the Web. SRID:4326 used for geography representation in postgis is based on EPSG:4326 (http://spatialreference.org/ref/epsg/4326/postgis/): INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 94326, 'epsg', 4326, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs ', 'GEOGCS[WGS 84,DATUM[WGS_1984,SPHEROID[WGS 84,6378137,298.257223563,AUTHORITY[EPSG,7030]],AUTHORITY[EPSG,6326]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.01745329251994328,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4326]]'); If I query Oracle 10g SELECT * FROM MDSYS.SDO_CS_SRS WHERE SRID='4326'; WGS 844326 4326 EPSG. See 3D CRS for original information source. GEOGCS [ WGS 84, DATUM [World Geodetic System 1984 (EPSG ID 6326), SPHEROID [WGS 84 (EPSG ID 7030), 6378137, 298.257223563]], PRIMEM [ Greenwich, 0.00 ], UNIT [Decimal Degree, 0.01745329251994328]] My understanding of decimal degrees is that 72 degrees 30 minutes are represented as 72.5, and that's the way I used to input coordinates in Oracle. Now if I want to input the same value in postgis, do I have to use a format such as 72d30m00s00 (sexagesimal degrees), or 72.5 (decimal degrees)? To add to the confusion, in postgis there is SRID:900914, which is the exact replica of Oracle's 8307, and both use the term decimal degrees. Thank you very much for your support. Michael The information contained in this transmission is to be considered CONFIDENTIAL and PROPRIETARY to Consortium Health Plans, Inc. and intended for the use of the Individual or Entity named above. If the reader of this message is not the Intended Recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is Strictly Prohibited. If you have received this transmission in error, please notify us immediately by telephone at 410-772-2900 or return email to sender immediately. Thank You. ___ 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] Postgres service refuses to start on windows
Roman, I'm afraid I have no ideas, but as it sounds like a PostgreSQL problem (and you may well get a reply from someone here) you might wish to try to PostgreSQL mailing list as well, if you are awaiting a reply. You might find a description of which version of XP, which version of PostgreSQL, what happened before you got this problem etc improves your chances of a useful response. cheers Ben On 19/06/2011, at 5:18 AM, Smith Roman wrote: hello ! I having an issue with my postgres database installed on a windows xp. For some reason, I can no longer start the postgres service through pgadmin or the services window. I get the following error: Error 1053: The service did not respond to the start or control request in a timely fashion. Any ideas ? Roman. ___ 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] Viewer plugins of PostGIS --HELP
Apostolis, This may not help, but I guess I find QGIS quite simple for actually visualising data. Maybe not so good for complex queries. There are now a number of PostGIS plug-ins though. Download. Install. Run. Add PostGIS Layer... cheers Ben On 15/06/2011, at 4:19 PM, ΑΠΟΣΤΟΛΟΣ ΛΕΛΕΝΤΖΗΣ wrote: Hi list For another time, i ask your help and your valuable experience!! My problem is related with ad-hoc solutions for viewing spatial data of a postgis database. I was searching a lot in the internet, and i found the following links which indicate the installation and the usability of a postgis-viewer plugin. [1] http://www.postgresonline.com/journal/archives/180-pgAdmin113plugins_postgis.html [2] http://geotux.tuxfamily.org/index.php?option=com_myblogtask=viewid=277Itemid=59lang=en Regarding with the first plugin[1], i have execute all the necessary guides for the installation, but it doesn't work. Specifically, i select a geometric table or i type a spatial SQL query, but when i switch the postgis viewer then the new window don't visualize the relevant table or the spatial outputs. So, could anyone help me?? Maybe, someone else has used this tool?? Concerning with the second plugin [2], i have installed it, very easy and quickly, but it doesn't work because it requires the installation of OSGeo4W environment. The postgis_viewer.bat file contains some variables which correspond to OSGeo4W paths. Because, i have installed seperate all these programs which are contained at OSGeo4W, i must change these paths for the good operation of plugin. So, could anyone to show me how to configure the postgis_viewer.bat file??? Any suggestion is accepted! Thanks in advance Apostolis ___ 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] Selecting Unique polygons with st_intersects()
Hi Regina,
On 07/06/2011, at 1:54 PM, Paragon Corporation wrote:
What about ST_Dwithin(q.the_geom, l.gda_geom,0.1)
and ST_Distance actually doesn't have as much short-circuiting as
ST_Dwithin since it has to compute the distance for matches --
ST_Dwithin in addition to having built in kicks out once it has concluded
the within distance criteria has been achieved.
Vey similar - looking at the Join filter it appears to be the same issue where
the planner is overly optimistic about the time it will take.
QUERY PLAN
-
Unique (cost=117.57..117.58 rows=1 width=405051) (actual
time=56098.368..56099.248 rows=2679 loops=1)
- Sort (cost=117.57..117.58 rows=1 width=405051) (actual
time=56098.367..56098.540 rows=2870 loops=1)
Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
Sort Method: quicksort Memory: 433kB
- Nested Loop (cost=0.00..117.56 rows=1 width=405051) (actual
time=38.165..56061.873 rows=2870 loops=1)
Join Filter: ((l.gda_geom st_expand(q.the_geom, 1e-05::double
precision)) AND _st_dwithin(q.the_geom, l.gda_geom, 1e-05::double precision))
- Seq Scan on lga l (cost=0.00..31.76 rows=10 width=400338)
(actual time=0.110..0.255 rows=10 loops=1)
Filter: (gid = ANY
('{245,247,252,254,258,259,275,279,289,297}'::integer[]))
- Index Scan using qldproperties_the_geom_gist on
qldproperties q (cost=0.00..8.28 rows=1 width=4713) (actual time=0.036..1.676
rows=359 loops=10)
Index Cond: (q.the_geom st_expand(l.gda_geom,
1e-05::double precision))
Total runtime: 56099.663 ms
(11 rows)
cheers
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting Unique polygons with st_intersects()
Thanks Regina, you are right - for some unknown reason I had in my mind that the order by clause would come after the distinct giving no surety of the part of the property being returned. For Posterity, I used : SELECT DISTINCT ON (pic) 'QLD'::text as state, lga_name09 as shire, propname as name, pic, round((st_area(st_transform(q.the_geom, 32754))/1)::numeric,2) as ha FROM lga l JOIN qldproperties q ON st_intersects(q.the_geom, l.gda_geom) AND l.gid in (245,247,252,254,258,259,275,279,289,297) ORDER BY pic, st_area(st_intersection(q.the_geom, l.gda_geom)) DESC; It's surprisingly slow, so I'll have to fiddle with the indexes, but it certainly does what I want. cheers Ben On 07/06/2011, at 8:56 AM, Paragon Corporation wrote: Ben, Perhaps I'm missing something -- but why don't you just put an ORDER BY in your DISTINCT ON to control which one is returned? SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic FROM lga l JOIN qldproperties q ON st_intersects(q.the_geom, l.gda_geom) AND l.gid in (245,247,252,254,258,259,275,279,289,297) ORDER BY pic, ST_Area(st_intersection(q.the_geom, l.gda_geom)) DESC Hope that helps, Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Monday, June 06, 2011 5:36 PM To: PostGIS Users Discussion Subject: [postgis-users] Selecting Unique polygons with st_intersects() G'day all, I've just realised that in selecting a group of properties by the local government area they reside in, I end up with duplicates = some properties span shire boundaries. I've used st_intersects, because some properties cover boundaries, so I need those that are within and may be partially without the boundary. I'm sure I'm not the first person to have this problem, but I was wondering if anyone has any insights into the most efficient way to choose a unique listing of property and shire. I was wondering about taking the area of the property still within the local government area, and choosing the row with the biggest value... Although I have unique property identifiers, I can't be sure of not getting a ridiculous answer - an erroneously small amount of a property in a shire etc. My current query looks a bit like : SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic FROM lga l JOIN qldproperties q ON st_intersects(q.the_geom, l.gda_geom) AND l.gid in (245,247,252,254,258,259,275,279,289,297); but I need to do better than using SELECT DISTINCT ON. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting Unique polygons with st_intersects()
Steve,
for fun... why not!
It actually took longer. I'm very bad at understanding query plans, but it
looks to me like the planner thought it would be quicker on less rows, but it
actually took much longer...
any thoughts would be appreciated - I've attached the queries and output below.
FWIW there are indexes on both gid fields (primary keys) and both geometry
fields (GIST)
Ben
On 07/06/2011, at 11:12 AM, Stephen Woodbridge wrote:
I know the newer functions are supposed to be index aware, but for grins, try
changing the ON clause to
ON q.the_geom l.gda_geom and st_distance(q.the_geom, l.gda_geom)=0.0
AND l.gid ...
Objects that intersect have to have a distance of 0.0 and the distance
function may have some faster algorithms than intersect.
-Steve
EXPLAIN ANALYZE SELECT DISTINCT ON (pic) 'QLD'::text as state, lga_name09 as
shire, propname as name, pic, round((st_area(st_transform(q.the_geom,
32754))/1)::numeric,2) as ha
FROM lga l
JOIN qldproperties q
ON st_intersects(q.the_geom, l.gda_geom)
AND l.gid IN (245,247,252,254,258,259,275,279,289,297)
ORDER BY pic, st_area(st_intersection(q.the_geom, l.gda_geom)) DESC;
QUERY PLAN
-
Unique (cost=312868.52..312871.53 rows=602 width=405051) (actual
time=19483.631..19484.592 rows=2673 loops=1)
- Sort (cost=312868.52..312870.03 rows=602 width=405051) (actual
time=19483.629..19483.828 rows=2861 loops=1)
Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
Sort Method: quicksort Memory: 432kB
- Nested Loop (cost=0.00..276.73 rows=602 width=405051) (actual
time=9.762..19446.773 rows=2861 loops=1)
Join Filter: _st_intersects(q.the_geom, l.gda_geom)
- Seq Scan on lga l (cost=0.00..31.76 rows=10 width=400338)
(actual time=0.124..0.262 rows=10 loops=1)
Filter: (gid = ANY
('{245,247,252,254,258,259,275,279,289,297}'::integer[]))
- Index Scan using qldproperties_the_geom_gist on
qldproperties q (cost=0.00..8.28 rows=1 width=4713) (actual time=0.036..1.680
rows=359 loops=10)
Index Cond: (q.the_geom l.gda_geom)
Total runtime: 19489.877 ms
EXPLAIN ANALYZE SELECT DISTINCT ON (pic) 'QLD'::text as state, lga_name09 as
shire, propname as name, pic, round((st_area(st_transform(q.the_geom,
32754))/1)::numeric,2) as ha
FROM lga l
JOIN qldproperties q
ON q.the_geom l.gda_geom and st_distance(q.the_geom, l.gda_geom)=0.0
AND l.gid IN (245,247,252,254,258,259,275,279,289,297)
ORDER BY pic, st_area(st_intersection(q.the_geom, l.gda_geom)) DESC;
QUERY PLAN
-
Unique (cost=1680.75..1680.80 rows=9 width=405051) (actual
time=56247.410..56248.288 rows=2673 loops=1)
- Sort (cost=1680.75..1680.77 rows=9 width=405051) (actual
time=56247.408..56247.586 rows=2861 loops=1)
Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
Sort Method: quicksort Memory: 432kB
- Nested Loop (cost=0.00..119.61 rows=9 width=405051) (actual
time=33.922..56211.975 rows=2861 loops=1)
Join Filter: (st_distance(q.the_geom, l.gda_geom) = 0::double
precision)
- Seq Scan on lga l (cost=0.00..31.76 rows=10 width=400338)
(actual time=0.050..0.212 rows=10 loops=1)
Filter: (gid = ANY
('{245,247,252,254,258,259,275,279,289,297}'::integer[]))
- Index Scan using qldproperties_the_geom_gist on
qldproperties q (cost=0.00..8.28 rows=1 width=4713) (actual time=0.034..1.639
rows=359 loops=10)
Index Cond: (q.the_geom l.gda_geom)
Total runtime: 56248.683 ms
(11 rows)
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Create circles using a formula
You could try : http://postgis.refractions.net/documentation/manual-1.5/ST_Buffer.html You might need something like : ST_MakePolygon(ST_ExteriorRing(ST_Buffer(tower.point, resultOfDistanceFunction))) cheers Ben On 02/06/2011, at 3:55 AM, vikashvikky wrote: Hi, I am doing a project for a telecom company. I have to render circles on the map(assuming the center of the circle as tower and circle as the area covered by that signal), I am using base layers as Google Maps,Yahoo maps(Spherical Mercator) So I have used CreateGeodesicPolygon() successfully to create circles by reading the values from DB. Now Since I have to render lot of circles, it made me switch to geoserver. Since the data is being provided by telecom company , they provide Lat,Lon,Gt,Gr,Pt,Pr,lambda(I think their meaning is irrelevant here) values. These values are stored in the database as they are. Now should use a equation(FRIIS transmission equation) and substitute each row(of a tower) values to find the distance covered by the tower.Then I have to create circles with that distance(considered as radius) and lon/lat and render them on map using geoserver. Please suggest me any ideas of how to carry on this task I wud be thankful to your guidance -- View this message in context: http://old.nabble.com/Create-circles-using-a-formula-tp31752667p31752667.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] Enter details into Geom column
James,
The error message has two possibilities:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
either their is no table called 'CLEANEDCAMDENGPS' (and I think you've probably
got the idea about the issues with case), but it is also possible that the
schema is not 'public', depending on your access to a database. If it is on
your local computer, and you have no idea what I'm talking about, then it
probably is public. If you are using a shared installation, it may not be
public.
if you are using psql (the command line), and try a command like \dt, you will
see a list of the 'relations' which do exist - something like
List of relations
Schema | Name | Type | Owner
+--+---+---
backoffice | access | table | ben
backoffice | accesslevel | table | ben
backoffice | categories | table | ben
if your table is not in this list, (it's specified as backoffice.access for
instance) then it may not exist, or you need to look up search_path.
cheers
Ben
On 31/05/2011, at 2:35 AM, James Smith wrote:
Dear Brent,
Thank you for your reply and simple explanation, it's much appreciated.
Unfortunately, it doesn't seem to work. When I try to create the Geom column,
I get this error:
-
ERROR: function st_creategeometrycolumn(unknown, unknown, unknown, integer,
unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Character: 8
-
So I browsed the PostGIS functions, and thought that I should perhaps use the
function 'AddGeometryColumn' instead, so changed the code to below:
select AddGeometryColumn('public', 'CLEANEDCAMDENGPS','geom',4326,'POINT',2);
However this returns an error of the below:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
I should perhaps say at this point, that the table called CLEANEDCAMDENGPS is
within a database called CAMDENGPS. I played around with trying to put the
database name into the statement too, but with no luck.
Any thoughts?
Thanks again, and yes, I'll take onboard your point about captials and table
names from this point forwards.
Cheers
James
On 29 May 2011 23:44, pcr...@pcreso.com wrote:
Hi James,
I suggest you avoid upper case letters in table column names if you can. It
makes a few things easier
The syntax in both SQL statements is wrong. Try:
select
ST_CreateGeometryColumn('public','CLEANEDCAMDENGPS','geom',4326,'POINT',2);
the fields are (in order):
schema where table can be found ('public')
the table name where you want the new column ('CLEANEDCAMDENGPS')
the name of the geometry column to create ('geom')
the SRID of the geometry column to create (4326)
the geometry type ('POINT')
the number of dimensions (2 - x y)
All string values need to be quoted.
To populate this column try:
update CLEANEDCAMDENGPS
set geom=setsrid(makepoint(LONGITUDE,LATITUDE),4326);
So, create a point geometry from the two numeric columns (makepoint), force
the SRID of this geometry to 4326 (setsrid), write this value to your new
column (update table set column =).
HTH,
Brent Wood
James Smith wrote:
Dear all,
Would appreciate some help. I have created an existing database (with
PostGIS extension) and it has a table called CLEANEDCAMDENGPS which
is populated with approx 600,000 rows. There are 20 or so columns in the
table, two of which are Latitude and Longitude (WGS84). I would now
like to create a Geom column with points in, the values of which
should be taken from the latitude and longitude column. Could someone
provide me with sample code as to how to do this please? I had a go
with the below, but don't really know what I'm doing... neither of the
statements work...
--CREATE THE COLUMN--
SELECT AddGeometryColumn('CAMDENGPS', 'GPS_POINTS', 'geom', 4326, 'POINT',
2)
--POPULATE THE COLUMN--
INSERT INTO CLEANEDCAMDENGPS (GPS_POINTS)
VALUES ( ST_GEOMFROMTEXT('POINT((SELECT LONGITUDE FROM
CLEANEDCAMDENGPS) (SELECT LATITUDE FROM CLEANEDCAMDENGPS))', 4326,
'Point'));
Thank you
James
___
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
Re: [postgis-users] Calculate variance of a multipoint
I'm not quite clear to me what you are trying to demonstrate - do you want to know the density of the points... relative to their total size (area / number?), or relative to some defined area? cheers Ben On 28/05/2011, at 6:19 AM, Aren Cambre wrote: Did anyone have thoughts on this? :-) Aren On Wed, May 4, 2011 at 2:12 PM, Aren Cambre a...@arencambre.com wrote: The more I think about it, is this a job for R? I know I need to start using R at some point, just haven't begun yet. Aren On Wed, May 4, 2011 at 1:42 PM, Aren Cambre a...@arencambre.com wrote: Suppose you have a geometry type with a multipoint. How would you calculate the variance of the points in that multipoint? I looked through the PostGIS 1.5 function reference and am not coming up with any easy way. A hard way seems to be using st_centroid(multipoint) to find the multipoint's center. From there, I can calculate the distance of each point from its center, and use that towards calculating the variance (each distance is squared, all squared distances are added together, then divide by number of points). I guess my ultimate need is to measure relative dispersion of multipoints. The multipoints that have the most dispersion are suspect, but I need a way of identifying which ones are like this. 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] AddGeometryColumn
Vittoriano,
Try it
without the empty '' for the schema (or specify the schema - probably
'public'),
and
the SRID should be an integer.
SELECT AddGeometryColumn('particelle','the_geom',3003,'MULTIPOLYGON',2);
might work.
(Assuming that you have loaded the postgis.sql functions into your database
first?)
cheers
Ben
On 26/05/2011, at 4:37 PM, vittoriano.aure...@libero.it wrote:
PostgreSQL 9.0
PostGIS 1.5
what is my problem?
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE particelle (gid serial PRIMARY KEY,
comune varchar(4),
sezione varchar(1),
foglio varchar(4),
allegato varchar(1),
sviluppo varchar(1),
numero varchar(9),
livello varchar(11));
SELECT AddGeometryColumn('','particelle','the_geom','3003','MULTIPOLYGON',2);
NOTICE: CREATE TABLE will create implicit sequence particelle_gid_seq for
serial column particelle.gid
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
particelle_pkey for table particelle
ERROR: function addgeometrycolumn(unknown, unknown, unknown, unknown,
unknown, integer) does not exist
LINE 12: SELECT AddGeometryColumn('','particelle','the_geom','3003','...
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
** Error **
ERROR: function addgeometrycolumn(unknown, unknown, unknown, unknown,
unknown,
integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to
add explicit type casts.
Character: 289
best regards
Vittoriano Aurelio
___
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 keep geometry_columns in sync wit tables and views (and new PostGIS 2.0 plans)
Thanks for the heads-up Regina, I'm not really over most of the issues with type etc, but from my perspective : I'm not a big fan of doing things because of specifications written in the past - I've never really understood the geometry_columns table as anything except a metadata table - and while I'm sure that there are advantages in terms of clients connection management, as someone who rarely has more than 50 -80 tables (each with only 1 or 2 geometry columns) and only Gigabytes of data, not Terabytes, since the introduction of functions like populate_geometry_columns(), I've not worried too much about it. It was a pain prior to that! My concerns (from my use case!) would relate to the risk that clients might struggle to find a table that doesn't exist, or isn't the one that is updated. I suspect that applications under current development would / could be changed, and those that are older may not support the update to 2.0 anyway. Probably better not to go the hybrid route - it might get worse than ugly. If you are going to make a change, I agree that a major version is the time to do it. We would probably selectively not migrate certain applications rather than going down the line of upgrading and rewriting code - I don't suppose that is a surprise to many people! cheers Ben On 20/05/2011, at 1:26 AM, Paragon Corporation wrote: Populate_Geometry_Columns is a function introduced in PostGIS 1.4. So yes you are right the probe_geometry_columns is a lighter weight that doesn't look at views and just looks at the constraints of tables. Speaking of this. In PostGIS 2.0, the plan is to use typmod support for geometry (like what we currently have for geography) as well and make geometry_columns a view instead of a table as it is now There are a couple of issues with this: 1) Existing data does not use typmod so there is a portability question of if people want to use the new geometry_columns should they be forced to convert their data to typmod. (I say no). 2) Exotic uses of geometry_columns that inspecting the system catalogs will not handle (e.g. views and other reasons for manual registration) Anyrate the thread is outlined here: http://trac.osgeo.org/postgis/ticket/944 I think the typmod is a done deal -- we are all in agreement we want this. What is not a done deal is how best to formulate geometry_columns view. I proposed a hybrid -- where part of the geometry_columns view reads from the system catalog and the other part reads from a static table (basically old geometry_columns table would be renamed and populate and so forth would be changed to add to this table). Anyway I admit the hybrid is less than pretty, but the alternatives look even more ugly to me from a migration standpoint and supporting more exotic uses. We'd be interested in hearing how people feel about these approaches and any other ideas as to how we can fuse the old with the new. Thanks, Regina http://www.postgis.us From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Wednesday, May 18, 2011 9:27 PM To: pcr...@pcreso.com; PostGIS Users Discussion Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views G'day Brent, I'm forever creating tables as subsets of existing tables so it is a truly useful function, however, I've suffered the same concerns - perhaps it is worth pursuing the name being changed? I've also never really understood the distinction between the populate_ and the probe_ functions? the probe_ one appears to be a 'lite' version, but it may have some other purpose that I don't understand? cheers Ben On 19/05/2011, at 9:02 AM, pcr...@pcreso.com wrote: I foubd this an unfortunately ambiguous name. it doesn't populate geometry columns so much as update the geometry_columns table. But irrespective of the name, it is nice to have :-) Cheers Brent Wood --- On Thu, 5/19/11, Ben Madin li...@remoteinformation.com.au wrote: From: Ben Madin li...@remoteinformation.com.au Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Thursday, May 19, 2011, 12:50 PM Ge, Try SELECT Populate_Geometry_Columns(); http://postgis.refractions.net/docs/Populate_Geometry_Columns.html which promises to truncate the geometry columns table first, then rebuild it. cheers Ben On 18/05/2011, at 8:05 PM, G. van Es wrote: Hi Edward, This will not work because this function doesn't do anything with views. Also stale records aren't removed. Ge --- On Wed, 5/18/11, Edward Mac Gillavry emacgilla...@hotmail.com wrote: From: Edward Mac Gillavry emacgilla...@hotmail.com Subject: Re: [postgis-users] how to keep geometry_columns
Re: [postgis-users] how to keep geometry_columns in sync with tables and views
Ge, Try SELECT Populate_Geometry_Columns(); http://postgis.refractions.net/docs/Populate_Geometry_Columns.html which promises to truncate the geometry columns table first, then rebuild it. cheers Ben On 18/05/2011, at 8:05 PM, G. van Es wrote: Hi Edward, This will not work because this function doesn't do anything with views. Also stale records aren't removed. Ge --- On Wed, 5/18/11, Edward Mac Gillavry emacgilla...@hotmail.com wrote: From: Edward Mac Gillavry emacgilla...@hotmail.com Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: postgis-users@postgis.refractions.net Date: Wednesday, May 18, 2011, 4:57 AM Hi Ge, You may want to check Probe_Geometry_Columns (http://postgis.refractions.net/docs/Probe_Geometry_Columns.html). Kind regards, Edward Date: Wed, 18 May 2011 04:38:51 -0700 From: gves2...@yahoo.com To: postgis-users@postgis.refractions.net Subject: [postgis-users] how to keep geometry_columns in sync with tables and views Hi All, We have a lot of tables and views updated, or better said, replaced on a daily basis. We have seen that under certain conditions (which are unclear) entries of the geometry_columns table are removed. So a mismatch occurs so now and then resulting in showing either no data or being very slow when an application has to do a table scan to obtain the geometry type. What I like to have is a procedure which checks all tables and views against the geometry_columns table and makes if necessary the right corrections. Before inventing the wheel again, does anyone know if this procedure already exist or knows perhaps another/better way to achieve this? Thanks in advance, Ge ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -Inline Attachment Follows- ___ 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 keep geometry_columns in sync with tables and views
G'day Brent, I'm forever creating tables as subsets of existing tables so it is a truly useful function, however, I've suffered the same concerns - perhaps it is worth pursuing the name being changed? I've also never really understood the distinction between the populate_ and the probe_ functions? the probe_ one appears to be a 'lite' version, but it may have some other purpose that I don't understand? cheers Ben On 19/05/2011, at 9:02 AM, pcr...@pcreso.com wrote: I foubd this an unfortunately ambiguous name. it doesn't populate geometry columns so much as update the geometry_columns table. But irrespective of the name, it is nice to have :-) Cheers Brent Wood --- On Thu, 5/19/11, Ben Madin li...@remoteinformation.com.au wrote: From: Ben Madin li...@remoteinformation.com.au Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Thursday, May 19, 2011, 12:50 PM Ge, Try SELECT Populate_Geometry_Columns(); http://postgis.refractions.net/docs/Populate_Geometry_Columns.html which promises to truncate the geometry columns table first, then rebuild it. cheers Ben On 18/05/2011, at 8:05 PM, G. van Es wrote: Hi Edward, This will not work because this function doesn't do anything with views. Also stale records aren't removed. Ge --- On Wed, 5/18/11, Edward Mac Gillavry emacgilla...@hotmail.com wrote: From: Edward Mac Gillavry emacgilla...@hotmail.com Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: postgis-users@postgis.refractions.net Date: Wednesday, May 18, 2011, 4:57 AM Hi Ge, You may want to check Probe_Geometry_Columns (http://postgis.refractions.net/docs/Probe_Geometry_Columns.html). Kind regards, Edward Date: Wed, 18 May 2011 04:38:51 -0700 From: gves2...@yahoo.com To: postgis-users@postgis.refractions.net Subject: [postgis-users] how to keep geometry_columns in sync with tables and views Hi All, We have a lot of tables and views updated, or better said, replaced on a daily basis. We have seen that under certain conditions (which are unclear) entries of the geometry_columns table are removed. So a mismatch occurs so now and then resulting in showing either no data or being very slow when an application has to do a table scan to obtain the geometry type. What I like to have is a procedure which checks all tables and views against the geometry_columns table and makes if necessary the right corrections. Before inventing the wheel again, does anyone know if this procedure already exist or knows perhaps another/better way to achieve this? Thanks in advance, Ge ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -Inline Attachment Follows- ___ 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 -Inline Attachment Follows- ___ 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] encryption of PostGIS databases
You can use the pgctrypto functions from contrib, but you might be better off securing the database and connections to it, rather than encrypting the data. Apart from the overhead of encrypting and decrypting the large volumes of data typically associated with geometry, I suspect your indexing would suffer. cheers Ben On 12/05/2011, at 8:30 PM, Malm Paul wrote: Hi, Is there a way to protect geographical data by encrypt the GIS db, if so that is the impact on performance? Kind regards, Paul ___ 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] Intersection query problem
George, You need to put source.track_cl into the table list for your query... hint - it is in the table list for your sub-select, but not for the main query, I know some people scorn indenting, but : SELECT gid, name, track_use, st_astext(clipped_geom) FROM ( SELECT source.track_cl.gid, source.track_cl.name, source.track_cl.track_use, (ST_Dump(ST_Intersection( extents.tiles.the_geom, source.track_cl.the_geom ))).geom As clipped_geom FROM source.track_cl INNER JOIN extents.tiles ON ST_Intersects(extents.tiles.the_geom, source.track_cl.the_geom) WHERE extents.tiles.name='BP33' ) As clipped WHERE st_geometrytype(clipped.clipped_geom) = st_geometrytype(source.track_cl.the_geom); cheers Ben On 11/05/2011, at 11:15 AM, George Washington wrote: Hi, I have the following query: SELECT gid, name, track_use, st_astext(clipped_geom) FROM (SELECT source.track_cl.gid, source.track_cl.name, source.track_cl.track_use,(ST_Dump(ST_Intersection(extents.tiles.the_geom, source.track_cl.the_geom))).geom As clipped_geom FROM source.track_cl INNER JOIN extents.tiles ON ST_Intersects(extents.tiles.the_geom, source.track_cl.the_geom) where extents.tiles.name='BP33' ) As clipped WHERE st_geometrytype(clipped.clipped_geom) = st_geometrytype(source.track_cl.the_geom); which gives me: ERROR: missing FROM-clause entry for table track_cl LINE 6: ...metrytype(clipped.clipped_geom) = st_geometrytype(source.tra... SQL state: 42P01 Character: 471 On the other hand this version of the same query works (only the last line differs): SELECT gid, name, track_use, st_astext(clipped_geom) FROM (SELECT source.track_cl.gid, source.track_cl.name, source.track_cl.track_use,(ST_Dump(ST_Intersection(extents.tiles.the_geom, source.track_cl.the_geom))).geom As clipped_geom FROM source.track_cl INNER JOIN extents.tiles ON ST_Intersects(extents.tiles.the_geom, source.track_cl.the_geom) where tiles.name='BP33' ) As clipped WHERE ST_Dimension(clipped.clipped_geom) 0; I cannot figure out what is wrong with the first query. Many thanks. George ___ 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_DWITHIN indices
Sairam,
On 06/05/2011, at 5:50 AM, Sairam Krishnamurthy wrote:
Table structure: lat AS double, lon AS double, spatialPoint AS point.
I have a query that uses ST_DWITHIN. I was under the impression that this
function will use the gist index on spatialPoint.
Index query:
CREATE INDEX table_spatial_index ON table USING btree (spatialPoint);
St_DWithin does use a bounding box if there are suitable indexes. I'm only
using 1.5, so things might be different if you are using 2.0 or 1.3, but I
think if you want to use a GIST index you need to create a GIST index, not a
b-tree. I thought - and I stress I'm not an expert on indexes that b-tree was
one-dimensional.
Select query:
SELECT lat, lon FROM table WHERE ST_DWITHIN(table.spatialPoint,
ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
Also, I'd caution against using ST_DWithin and non-projected data systems. you
won't actually be seeing a circle, even if you do appear to be close to the
equator.
cheers
Ben
Query plan:
EXPLAIN SELECT lat, lon FROM EVI250m WHERE
ST_DWITHIN(EVI250m.spatialPoint,
ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
QUERY PLAN
-
-
Seq Scan on EVI250m (cost=0.00..4757082.00 rows=1 width=16)
Filter: ((spatialPoint
'010320E610010005A0224DB03F00C05D6B324000A0224DB03F0020EE6B324000C050DDB03F0020EE6
) AND _st_dwithin(spatialPoint,
'010120E6102A7288B83995B03FCFF753E3A56B3240'::geometry,
0.0011::double precision) AND ('010120E6102A7288B83
recision)))
(2 rows)
Any thoughts ?
Thanks,
Sairam Krishnamurthy
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading Data / Refreshing every 5 minutes
My very uneducated perspective would be that it depends on how much of the data (ie what sort of subset) the user is requesting. If they are just getting the whole table every time, there may be limited benefit to large amounts of indexing. If you are loading the radar shapefile for continental Australia, but your users are on the coast somewhere between Exmouth and Karratha, then I think an index is probably essential for any sort of performance. As a vague point of interest, why are you converting the shapefile to postgis for 5 minutes, then deleting the whole lot - there might be very little speed benefit to doing this. Could you not just load the new data into the same table (ie append) with a field (something like createdon timestamp not null default now() or similar) for the date and time. Then the user request could be modified to get the most recently loaded data... or loops of the last 20 minutes... or something like that. Just a thought, it might save the blank pages. cheers Ben On 03/05/2011, at 11:17 AM, Chris Brisendine wrote: Every once and a while when a wms user (on Geoserver) goes to refresh the view (Latest Radar Image) they get a blank page, just catching it when there is no data in the table. There is not any data verification on my end its just a simple conversion of a polygon to postgis for geoserver to use. Do I need to worry about indexing and vacuuming since the data changes so much? I am very new to pgsql and still trying to learn everything... ___ 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] PSC Vote to officially drop support for PostgreSQL 8.3 in PostGIS 2.0
another 2c (AUS) worth - but that's 2.1 US cents at the moment! Another strictly user, but I'll go along with Brent's sentiment on this - we are lucky that our servers are all FreeBSD - meaning that we are now running the 9.0.3 / 1.5.2 combination straight out of ports. Upgrades (just went from 8.3 to 9.0) have gone smoothly. I wouldn't say we are early adopters, but our upgrade decisions are now driven by reducing providing backward compatibility... so I would be comfortable with the suggestion of not trying to provide backward compatibility to everyone. I would however suggest that although I understand Regina's limit of 3, I think maintaining 8.4 support may have to exist beyond the release of PG9.2, as the move from 8.4 - 9 is probably far more difficult that 8.3 - 8.4. cheers Ben On 22/04/2011, at 10:59 AM, Stephen Woodbridge wrote: Brent, Well said! I was trying to frame this same sediment. Devs, I'm on the mapserver PSC and while I am a strong advocate for user issues and release compatibility, I will be one of the first to say if a major release is making things faster, better, decreasing maintenance at the cost of breaking backwards compatibility, then we should do that. The incentive for users to upgrade is based on there being lots of better, faster, quality features that they do not have on the old releases. Given what I have heard so far, I have old versions I can use if I have to, and there seems to be lots of goodness to offset the pain of upgrading. So 2.0 is the time to do this. Waiting until 3.0 will probably not a good idea. Thanks for everyone time and efforts on building such a great product! -Steve On 4/21/2011 9:35 PM, pcr...@pcreso.com wrote: 02c worth strictly from a user's perspective: New users will generally start with current latest versions. So they should be fine. Old users who have difficulty upgrading. (Oft times me :-) my call. If I need Postgis to work with 8.3, I use v1.5, if I need later Postgis functionailty, I upgrade. I still have a choice, as long as the older versions are available, even if they are no longer officially supported. The rate of development of Postgis Postgres is great. I'd sooner see the developers free to develop, making the most of their valuable appreciated time, rather than spending time just keeping older Postgres versions supported. Thanks everyone! Brent Wood --- On *Fri, 4/22/11, Paragon Corporation /l...@pcorp.us/* wrote: From: Paragon Corporation l...@pcorp.us Subject: Re: [postgis-users] [postgis-devel] PSC Vote to officially drop support for PostgreSQL 8.3 in PostGIS 2.0 To: 'PostGIS Development Discussion' postgis-de...@postgis.refractions.net Cc: 'PostGIS Users Discussion' postgis-users@postgis.refractions.net Date: Friday, April 22, 2011, 12:57 PM Mark, Agree with Paul -- we did say all PSC should at least feel comfortable with our position and be able to defend it. Though probably something we need to clarify in our voting rules. I've cc'd the regular users group since I feel they would be most affected by this decision and would like to hear their opinions on it. First let's keep things in perspective. We are talking about not having support for PostgreSQL 8.3 for PostGIS 2.0. We will still do our duty and support PostgreSQL 8.3 on PostGIS 1.3-1.5 and if we don't have to worry about also supporting it on 2.0, we'll have many more cycles to support issues that arise in 1.3-1.5. More food for thought - From all the signals I have seen, I just feel trying to support PostgreSQL 8.3 on PostGIS 2.0 is a really bad idea. I will add this. It's not just the testing, it’s the fact that requiring our 2.0 code work on PostgreSQL 8.3 is going to slow our release as all PostGIS developers will need to limit their feature set to work on 8.3 and avoid new features that will make programming easier and more efficient. We have much more plpgsql code in PostGIS 2.0, than we have ever had in prior versions, which makes the task much more difficult. From what I can gather most distros package just one version of PostGIS with each version of PostgreSQL if they package PostGIS at all. I just helped a client port their database to an ubuntu server on a different host and the stable on Ubuntu 10 is 8.4 with PostGIS 1.4. In fact even the backports that have PostgreSQL 9.0, I can't find 1.5 so had to compile ourselves to get 1.5. This is not something most users new to PostgreSQL or PostGIS will be willing to do. So the reality is if they want to stay stable they'll be using 8.2 with 1.4. Similar story with centos. Yum rpms packages just one version of PostGIS with 8.4 and 9.0. For 9.0 it's 1.5. If we don't make release before the PostgreSQL 9.1 cut
Re: [postgis-users] create table sintax
Júlio, The problem is probably not a postgis problem (although the constraints may be geometry type related), but a postgres procedural language problem. However, you haven't provided much information, no example, and you have just kept re-posting it. Even changing the email subject didn't really help much. I would suggest that you provide a better example of what is going wrong for you, rather than leaving it to the imagination of other readers. (Although many of those who do work this list are miracle workers when it comes to providing answers, I'm not!) Sorry if this sounds a bit abrupt, but normally in mailing lists if you haven't received a reply to your question, it means no one understands the problem. Re-posting the same question probably won't help. cheers Ben On 19/04/2011, at 4:46 PM, Júlio Almeida wrote: Hello, If I run create table newtable (like oldtable including constraints); in the SQL window with works just file. But if i execute execute 'create table '||newtable||' (LIKE '||oldtable||' including constraints)'; inside a function, in a LOOP, the constraints aren't created. What is the problem? Tanks, julio almeida ___ 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] images in postgresql
Robert, On 06/03/2011, at 4:28 PM, Robert Buckley wrote: The windturbine table exists in EPSG:4326. I made a seperate table for the images because I didn´t wan´t to blow the size of the wind turbine table out of proportion and jeopardize performance. My understanding - and if I'm wrong I need to know(!) - is that the sort of data you are talking about (large geometries or blobs - for your pictures) are not stored in the primary table, but in associated storage space, known as TOAST tables. This has important implications for indexing, but is brilliant because the content of these data fields does not directly impact on the number of pages that the table takes, hence rapid searching is still possible. cheers Ben I am making a simple application to show wind turbines as wms and I wanted to show the turbine in a popup. I´m not sure how to get the popup to display though. Any examples? Thanks, Rob Von: Paragon Corporation l...@pcorp.us An: PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: Samstag, den 5. März 2011, 18:21:49 Uhr Betreff: Re: [postgis-users] images in postgresql Robert, Is there a reason why you have the points in a separate table or do you have points in both tables and you want to relate by a spatial join? If its a 1 to 1 relationship, we would just put them in the same table. As far as foreign keys go, you should have some identifier the same in the two tables. Do you? So it would be of the form SELECT wt.wt_id, wt.geom, p.picture FROM windturbines As wt INNER JOIN pictures As p ON wt.wt_id = p.wt_id or if they are spatially related by space SELECT wt.wt_id, wt.geom, p.picture FROM windturbines As wt INNER JOIN pictures As p ON ST_DWithin(wt.geom, pt.geom, 10) The 10 depends on the spatial reference system or if you are using geography type then it means 10 meters. So I'm treating the wind turbine location and picture location as the same if they are within 10 meters apart. BTW: you might want to read the first chapter of our upcoming book. It's a free download and answers this type of question with concrete examples. http://www.postgis.us/chapter_01 Leo http://www.postgis.us From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Robert Buckley Sent: Saturday, March 05, 2011 5:39 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] images in postgresql Hi, I am just experimenting at the moment with a project and could do with some advice. I have created a database which contains photos of Windturbines. I also have a postgis database with the locations (points) of the wind turbines and would like join the photos to the points via a link table or foreign key. As you can tell, I haven´t too much experience with postgresql and relational database design. But i can imagine that the task should not be too difficult. I am just a bit unsure how to go about it. The photos are on the linux server and the creation of the table and the insert of the image was successfull. But how do i get the join and how would I display this photo in a geoext project? thanks for any tips, Robert ___ 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_DWithin on 2 columns which are the same
Robert, On 01/03/2011, at 4:47 PM, robertvc wrote: I have been trying, with no luck, to implement a query to return me all the pairs of rows that are within a certain range of each other. I searched the forum before trying to find a solution but I haven't been able to. Sorry if there is a solution to this problem already that I didn't see. I think you need to search on nearest neighbour. Suppose you have a 2 column table cars with an integer ID, and a geography Location representing the position of the car globally. I want to write a query that will return me pairs of IDs of cars that are within say 1km of each other. Having looked at the functions available ST_DWithin seems the obvious choice but I haven't been able to actually use it for what I want. I've started out by simply testing if it matches each car as being in range with itself : SELECT * FROM cars WHERE ST_DWithin(location, location, 1); This returned all the entries from the cars table as expected (given that no cars where actually within a meter of each other). This doesn't sound like my interpretation - it returned all the entries because each car's location was within 1 metre of itself (unsurprisingly). I then tried to find all the cars that are within a km of each other but don't have the same ID (to avoid matching a car with itself). Because of this extra constraint I need to somehow treat the location columns individually so I've tried the following: SELECT c1.id, c2.id FROM cars AS c1, cars AS c2 WHERE ST_DWithin(c1.location, c2.location, 1000) AND c1.id != c2.id; But this query never actually finishes computing (the number of entries in my cars table is around 30k and after 2 hours of the query being executed I still didn't get back a result). I would greatly appreciate any help in computing this query as well as any tips on performance. I should also probably mention that I did an indexing on the location column as suggested in the documentation: CREATE INDEX cars_gix ON cars USING GIST (location); Did you also VACUUM ANALYZE after creating the index? Performance-wise, using geometry instead of geography might help. You could try EXPLAIN to see where the slow point in the query is. How widespread are the cars - are you talking about 30 000 cars that might all be within 1 km of each other? Given that the direction of the relation doesn't matter maybe use an outer join something like SELECT c1.id, c2.id FROM cars c1 LEFT OUTER JOIN cars c2 ON ST_DWithin(c1.location, c2.location, 1000) AND c2 IS NOT NULL AND c1.id c2.id; (I have no idea if that would work!) cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speed of query
Puneet, I'm thinking you are doing a lot of extra calculations here by constantly transforming the points. Why did you turn a geometry into two points and then make the geometry again... this would invalidate the use of an index on the same column? (Neither did you define a SRID for your box in the first query) Have a look at : file:///usr/local/pgsql/share/doc/contrib/postgis-1.5/postgis.html#ST_MakeBox2D I think the query (slightly modified here) --Return all features that fall reside or partly reside in a US national atlas coordinate bounding box --It is assumed here that the geometries are stored with SRID = 2163 (US National atlas equal area) SELECT feature_id, feature_name, the_geom FROM features WHERE the_geom st_transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)),4326),2163) is what you are after (obviously you will have to transform the lat long points (or the box. cheers Ben On 02/03/2011, at 3:40 AM, Puneet Kishor wrote: I have a table with ~ 13.25 million points. CREATE TABLE ll ( gid serial NOT NULL, latitude double precision, longitude double precision, a integer, b integer, the_geom geometry, CONSTRAINT ll_pkey PRIMARY KEY (gid), CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK ( geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL ), CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163) ) WITH ( OIDS=FALSE ); I want to select the columns a,b for the rows that lie within a box made by points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results -- Query 1 SELECT a, b FROM ll WHERE ST_Within( ST_Point( ST_X(ST_Transform(the_geom, 4326)), ST_Y(ST_Transform(the_geom, 4326)) ), ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) ) 31 rows returned in 46125 ms Query 2 SELECT a, b FROM ll WHERE ST_X(ST_Transform(the_geom, 4326)) = -91.048 AND ST_X(ST_Transform(the_geom, 4326)) = -90.973 AND ST_Y(ST_Transform(the_geom, 4326)) = 45.956 AND ST_Y(ST_Transform(the_geom, 4326)) = 46.007 31 rows returned in 25729 ms Query 3 SELECT a, b FROM ll WHERE longitude = -91.048 AND longitude = -90.973 AND latitude = 45.956 AND latitude = 46.007 31 rows returned in 4011 ms Query 4 I also have the same data in a SQLite database with an R*Tree index on lat/lon. A query analogous to Query 3 returns fast enough to not even register a time... a few milliseconds; effectively 0 seconds. What gives? -- Puneet Kishor ___ 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] Find n Nearest Neighbors for given Point using PostGIS?
Have you tried EXPLAIN to see where the slow part is? But at a guess - consider that st_dwithin uses the geometry unit for it's calculations - so you are searching for everything within 300 degrees (more than halfway around the planet). You may want to try searching a smaller set of data before you sort it to find the closest five. cheers Ben On 25/02/2011, at 12:04 PM, Scholle wrote: I am trying to solve the problem of finding the n nearest neighbors using PostGIS: Starting Point: - Table geoname with geonames (from geonames.org) containing latitude/longitude (WSG-84) - Added a GeometryColumn geom with srid=4326 and datatype=POINT - Filled geom with values: UPDATE geoname SET geom = ST_SetSRID(ST_Point(longitude,latitude) 4326); - Created GIST index for geom (CREATE INDEX geom_index ON geoname USING GIST (geom);) / Clustered geom_index: CLUSTER geom_index ON geoname;) - Created PRIMARY KEY UNIQUE BTREE index for geonameid Problem: Find n (e.g. 5) nearest neighbors for a given Point in table geoname represented by id (geoname.geonameid. Possible solution: Inspired by http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor, I tried the following query: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid + AND ST_DWithin(start.geom, ende.geom, 300) order by distance limit 5 Processing time: about 60s Also tried an approach based on EXPAND: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid AND expand(start.geom, 300) ende.geom + order by distance limit 5 Processing time: about 120s The intended application is some kind of autocomplete. So, any approach taking longer than 1s is not applicable. Is it generally possible to achieve such a response time with PostGIS? -- View this message in context: http://old.nabble.com/Find-n-Nearest-Neighbors-for-given-Point-using-PostGIS--tp31010122p31010122.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] SPAM blocking on postgis server
G'day all, I just tried to send a response, and my ISP (who's SMTP I normally use) rejected it : Reason:5.7.1 Service unavailable; Client host [61.9.189.137] blocked using dnsbl.sorbs.net; Currently Sending Spam See:http://www.sorbs.net/lookup.shtml?61.9.189.137 I doubt my ISP really cares, but it might affect the list? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Missing primary key column.
Byron, A little bit more information may help here - what do you mean by 'importing' the table into QGIS? Have you checked that the primary key is unique - and such a constraint exists on the table definition? and that the column is in the geometry_columns table - you may want to try populate_geometry_columns() function. maybe post the output of \d tablename. cheers Ben On 09/01/2011, at 9:46 AM, Byron Como wrote: The problem: Missing primary key column in postgresql table imported into Quantum GIS. Details: 43,000 object database created using Windows XP 32 bit, postgresql 8.4, postgis 1.4, Quantum GIS 1.5. Daily backups using pgadmin. Several db restores were performed on the XP system for various reasons (one time was to recover from a lightning strike). Restored data was good and project continued with no problems. Recently upgraded to Windows 7 64 bit to be current and take advantage of the increased memory available. Installed the same software as above. The data in postgresql seems ok and can be manipulated normally - copied, pasted, deleted. Importing a table into Qgis 1.5 or 1.6 results in layer with the primary key column missing. The data can't be manipulated completely. The table can be added to but deletions can't be performed. The primary key is gid, integer. Corrective actions thus far: Reloaded software many times giving great attention to the details of installation. Examined the data in pgadmin for anomalies. Nothing apparent. All seems correct. Posted to Quantum GIS mailing list. Nothing there. Expected resolution: Barring solution from mailing list suggestions, reinstall on Windows XP 32 bit and continue with memory limitations as before. (Extra memory desired to make very large format high-resolution prints). ___ 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] Calculate Average Distance (w/ LIMIT)
Andreas, I can't improve on Nicolas' suggestion, but at a guess you might also want to look at the spatstat package in R, which has defined a number of functions for this sort of analysis, and can include windows (ie country polygons) to incorporate edge correction - ie F, G and K functions. cheers Ben On 03/01/2011, at 9:05 PM, Nicolas Ribot wrote: On 6 December 2010 13:19, Andreas Forø Tollefsen andrea...@gmail.com wrote: Hi all. I have two point data sets. One point set is health observations, the other one is conflicts. What i want to do is to calculate the average distance from each health observation to the 10 nearest conflict points. I have managed to calculate the average distance, but only to all conflict points. My query: select LBGE51FL.the_geom, LBGE51FL.gid, AVG(ST_Distance(ST_Transform(LBGE51FL.the_geom, 954010), ST_Transform(acled.the_geom, 954010)))/1000 AS dist INTO LB_dist FROM LBGE51FL, acled WHERE acled.gwno = 450 GROUP BY LBGE51FL.the_geom, LBGE51FL.gid; SRID 954010 is Eckert VI. Any idea on how to limit this query to the 10 nearest points? Thanks. Andreas Hi, Using a subquery to compute the distance, order by distance, limit 10, before computing the avg distance ? Nicolas ___ 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] Traverse set distance along a multiline becoming multiple storage columns?
I've done it this way, but now may be the time for me to check... I have often wondered if because the Geometry is stored in a TOAST table, this shouldn't impact on speed should it? cheers Ben On 28/12/2010, at 5:57 PM, pcr...@pcreso.com wrote: Hi Aren, In this sort of case I usually prefer to keep my source data as a reference, as well as an indexed reprojected (working) version of the geometry. Instead of doing it as you have done, and create a new table, I add a new geometry column of the appropriate type SRID to the original table, then populate it using an update ( don't forget to index it): eg: select ST_AddGeometryColumn( '','txdot_roadways','geom_nad',3081,'LINESTRING',2); update txdot_roadways set geom_nad=ST_Transform(the_geom, 3081); ( if your original geometry is a MULTILINESTRING, then use that type instead) This keeps the two geometries together in the same table, something that is non-trivial in a traditional GIS, but just another column in spatially enabled database. If you really don't need to keep the original column, you can always drop it from the table after creating the 3081 version. Having a second table also works, but I figured I'd mention this alternative. Cheers, Brent Wood --- On Tue, 12/28/10, Aren Cambre a...@arencambre.com wrote: From: Aren Cambre a...@arencambre.com Subject: Re: [postgis-users] Traverse set distance along a multiline? To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Tuesday, December 28, 2010, 6:50 PM Thank you. Now my shp2pgsql conversion results in a PostGIS table with an SRID. I then ran this query to reproject the data into a new table: INSERT INTO txdot_roadways_3081_transform SELECT [all other fields go here], ST_Transform(the_geom, 3081) as the_geom FROM txdot_roadways; Loading into qgis, the map now looks like a correct projection for taking planar (?) measurements. Previously the state looked as if it was stretched horizontally, but I guess that's to be expected if longitudinal lines don't bend. Thanks again to both of you for helping with this. It never occurred to me how easy it can be to reproject GIS data. Aren On Mon, Dec 27, 2010 at 6:15 PM, Paul Ramsey pram...@opengeo.org wrote: Right, use 4269, that's a good NAD83-geographic-coordinates number. Import with shp2pgsql -s 4269 and go from there. P On Mon, Dec 27, 2010 at 3:54 PM, Aren Cambre a...@arencambre.com wrote: Brent and Paul, Thank you for your help! So here's my (new) dilemma--my PostGIS table doesn't appear to have a projection specified, and I am not clear how to get to one. I don't think it has a projection because this table's corresponding entry in the geometry_columns table has -1 for the srid column. This ShapeFile's PRJ file has this: GEOGCS[GCS_North_American_1983,DATUM[D_North_American_1983,SPHEROID[GRS_1980,6378137.0,298.257222101]],PRIMEM[Greenwich,0.0],UNIT[Degree,0.0174532925199433]] I'm not seeing a clear match between this and any projection. Some Google searching suggests this may be 4326, but I'm not sure about this. And if I modify geometry_columns and import the PostGIS table into QGis, I get this QGis error: 1 cursor states lost. SQL: CLOSE qgisf0 Result: 7 (ERROR: current transaction is aborted, commands ignored until end of transaction block ) If I revert that field back to -1, the error goes away on next import. When I imported using shp2pgsql, I didn't use the -s switch. I presumed it would catch the projection automatically. I'm at a loss to know what to do next. I guess I need to figure out what the true SRID of this data is before I can do any re-projections? Aren On Mon, Dec 27, 2010 at 12:22 AM, Paul Ramsey pram...@opengeo.org wrote: You need to do your analysis in a projected coordinate system, not geographics. CREATE TABLE my_new_texas_roads AS SELECT ST_Transform(the_geom, 3081) as the_geom, other_attributes FROM texas_roads; EPSG:3081 should be a good coordinate system for working with your Texas data. http://spatialreference.org/ref/epsg/3081/ Not that the units are meters, so perform the appropriate linear transformations when looking for mile markers. Paul On Sun, Dec 26, 2010 at 4:35 PM, Aren Cambre a...@arencambre.com wrote: I am trying to determine mile markers along Texas highways. My starting point is the ShapeFile TxDOT Roadways 2010 at http://www.tnris.state.tx.us/datadownload/download.jsp. I've used shp2pgsql to get it into a PostGIS 1.52-enabled Postgres 9.01 database. I naively thought I could just figure out the number of miles per unit of latitude and then traverse each roadway, one mile at a time, using ST_Line_Interpolate_Point. However, predictably, the more longitudinal a route, the more error it shows when I compare my calculated mile markers to what Google Maps shows. Again, this is
Re: [postgis-users] Shifting linestrings left
G'day Puneet, On 24/12/2010, at 2:17 AM, Puneet Kishor wrote: Sean wrote: Is there a way to 'shift' the linestring to the left - after all, that would be sensible side of the road to drive on... Which world do you live in? After all, the right side of the road is called the right side for a reason. I thought my accent would have given that away by now! ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Shifting linestrings left
G'day Sean, On 24/12/2010, at 2:07 AM, Sean wrote: Is this just a visualization issue? You could store copies of the the road line with attribute data indicating direction or have a separate table with foreign keys to the geography and the direction data. Then, you just symbolize differently based on the attributes. Offset the symbology not the geometry. This is more correct, it preserves the information about the actual road on which they travel. Showing direction of travel is strictly a symbology issue. It's a mix of both - initially, it was a geometry issue - I have linestrings for roads, and for some I have information that suggests that they are dual carriageway, and I was wondering if we could approximate the dual carriageway by shifting the original geometry a little bit to the left going each way. Then I realised I had better things to do, so I moved on, but now I have a need to show routes going both ways, but using pgRouting they (unsurprisingly) following the same lines, and I wanted to show it. Using QGIS I offset the symbology, but it doesn't actually seem to works how I wanted (which is nicely described by Simon's post), but rather looks like : inline: Screen shot 2010-12-24 at 9.30.19 AM.png cheers (and Happy Christmas etc) Ben___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Shifting linestrings left
G'day all, I have a problem where I am trying to show the route of livestock movements, but I am loosing information when the travel along the same road in different directions (ie in some cases they travel from a saleyard to a feedlot, then back to the saleyard after a period). The roads they travel along are single linestrings. Is there a way to 'shift' the linestring to the left - after all, that would be sensible side of the road to drive on... cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Shifting linestrings left
Thanks Brent, Happy Christmas / New Year to you as well! On 23/12/2010, at 2:07 PM, pcr...@pcreso.com wrote: ST_Translate() is the simplest, but if your lines are horizontal you may want a Y shift as well? http://postgis.refractions.net/documentation/manual-svn/ST_Translate.html Lines are roads, so they go every which way. Having done this, I suggest you also use ST_reverse() to reflect the direction is now going the other way (unless it already is :-) http://postgis.refractions.net/documentation/manual-svn/ST_Reverse.html For more complex shifting of features, probably overkill in your case, see http://postgis.refractions.net/documentation/manual-svn/ST_Affine.html This might be what I am after, but I was thinking of somehow translating each segment parallel to it's current alignment and then rejoining with a nice smooth curve... kind of like a 1-sided buffer. cheers Ben --- On Thu, 12/23/10, Ben Madin li...@remoteinformation.com.au wrote: From: Ben Madin li...@remoteinformation.com.au Subject: [postgis-users] Shifting linestrings left To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Thursday, December 23, 2010, 3:13 PM G'day all, I have a problem where I am trying to show the route of livestock movements, but I am loosing information when the travel along the same road in different directions (ie in some cases they travel from a saleyard to a feedlot, then back to the saleyard after a period). The roads they travel along are single linestrings. Is there a way to 'shift' the linestring to the left - after all, that would be sensible side of the road to drive on... cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] import shape or sql to postgres automatically
Can you not just pipe it in : (from the manual)
shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb
or use the \i metacommand to run the created script.
cheers
Ben
On 13/12/2010, at 10:22 PM, zhang zhe wrote:
Hello,
Does anyone know how to import sql table or shape file to postgres database
automatically?
What I did before is:
I have shpe file save in C:/users
I go to terminal in linux, and go to the directory C:/users
C:/users shp2pgsql shapefile.shp sqlfile.sql
I will get sql file saved into c:/users directory. I open the sql file copy
all the sql query. After that I open postgres database, and paste all the
query to sql query window, and run the query. I will have new table appear in
the dabase.
Sometimes the sql table is too large, that I cannot paste them all in the
postgres sql query window. Postgres is always get stuck because of the query
is too large.For instance I have road dataset of whole contry, and it has
really millions of queries. Is there any way to import shape file or sql
table to Postgres database automatically without any manual work?
I am now developing a application in php. In php, is there method to import a
sql table or shape file to Postgres automatically? I know with php there is
way to connect postgres and run the query.
$query 1=import sql table to postgres;
pg_query($query1) or die ('query1 failed'. pg_last_erro());
Is there any query could import sql table to postgres automatically?
Thanks a lot:)
___
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 geometry when using select AsText
Ramses, On 06/07/2010, at 17:37 , rdelisabeth wrote: My problem is the following. I also have some data from my colleagues from Spain. Their data supposedly is in WGS1984 epsg 4326. So I transformed it to 900913 as I did with my other data. So far so good. BUT when I do a SELECT AsText from one of the geometries I get the following: MULTIPOLYGON(((inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf, ...etc etc ... inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf,inf inf),(inf inf,inf inf,inf inf,inf inf,inf inf))) I think this means that the geometry is invalid. When I read it from my application using Hibernate spatial I get an error like invalid geometry. I wonder if you have data from a UTM Grid - do the values look like reasonable longitude and latitude values (in degrees) for Spain. I'm on the wrong side of the planet to be sure what is right, but if you have any values greater than about 45, I think you probably have WGS84 UTM values, which would be in the 10's to 100's of thousands. instead of 4326 try something like 32630... just a guess. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with probe_geometry_columns()
Fred,
On 18/05/2010, at 22:38 , Fred Lehodey wrote:
I have no success trying the function probe_geometry_columns() with Postgis
1.5.0
1) Not sure but comparing the SQL with Postgis 1.3.3:
the clause (in the INSERT step and not the count of probed)
sridcheck.consrc LIKE '(srid('||a.attname||') = %)' in postgis 1.3.3
is now:
sridcheck.consrc LIKE '(st_srid('||a.attname||') = %)' in postgis 1.5.0
This looks like a tipo error. (this is not the function here but the
constraint text in pg_constraint)
I think the st_ prefix is now required,
2) I have a second problem with pg_constraint table and the consrc field.
Most of time I have something like :
(public.srid(the_geom) = 27492)
and not (as expected by the function probe_geometry_columns()) :
(srid(the_geom) = 27492)
This was previously an issue if you installed postgis into other than the
public schema. The public schema reference was in a few locations, so you need
to search it out in the function defs and remove it and recreate the function
if you don't want to upgrade.
I have upgraded a number of databases to 1.5 from 1.4 and it seems to have
fixed it... but I have also mucked it by not changing the search_path prior to
running the upgrade, leaving me with multiple postgis function definitions!
cheers
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] List pre-emption.
G'day all, I have many problems, (and don't want to dwell on them) but I am wondering if I am the only person who routinely receives list emails in the wrong order - for instance, I have just replied to request which arrived at 11:24 (only a few minutes ago) only to discover that others have also replied, at 11:09, 10:44 and 10:24. Just being in the Southern Hemisphere doesn't explain this, nor being Australian. Is it a gold membership thing to have your questions answered before you submit them...where do I sign up? More seriously, I haven't noticed this effect with other (albeit lower volume) lists that I subscribe to? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with postgis querybypoint in mapserver-php
Guillermo,
MapServer is the proper list for this question, but you haven't given much
information on what is a complex issue... and I can't help you directly with it
I'm sorry, but it might help if you gave a bit more information.
Are you getting a map? Is the data correctly displayed? You could get attribute
data either from the data in your query (use * in the format below if in doubt)
or if you take the gid and look it up separately.
The MapServer Docs at http://mapserver.org/input/vector/postgis.html show an
example of a query:
CONNECTIONTYPE POSTGIS
CONNECTION dbname=yourdatabasename user=yourdbusername
DATA the_geom from (select g.gid, g.the_geom, a.attr1, a.attr2 from geotable g
join attrtable a on g.gid = a.aid) as subquery unique gid using srid=4326
A common mistake is a mismatch between the projection systems - are they both
displayed?
Have you been logging the php and MapServer output (DEBUG level 1 or more) to
see what is happening when you click on a point?
Having said that, I notice you have suppressed any error messages on
@$qlayer-queryByPoint($point, MS_MULTIPLE, $radius);
so you may not get much help from the php log.
hopefully someone else might be able to help, good luck.
cheers
Ben
On 17/05/2010, at 15:09 , Guillermo Tamburini Beliveau wrote:
Hi,
first, I would like to apologize for possible confussions with my english or
my
limitated knowledge of programming and the explanations on this.
And second, sorry if I insist, but I trided two times in the mapserver list
and
now, the second time in this, and nobody responds me. please, if somebody
knows
anything about my question, please say something. Thank you.
I would ask to the list if someone knows the reason of a problem that I'm
having with the php-mapscript methods for the query methods in the postgis
layers.
Indistinctly for querybypoint or querbyrectangle, my code (different versions
of
it), works perfectly with shapes, but it always fails with the postgis layers.
When calling to the shapeindex member of the ResultCacheMemberObj obtained, it
always returns 0, as is contrary to what happens with the shapes, where it
always returns the correct shapeindex. Then, it is impossible to acces to the
desidered feature, and as you know, this are usal and simple actions.
May I have to add more columns to the select of the .map file for getting the
features atributes like in the example (I don't think so)?
DATA geom FROM puntos using unique gid
#DATA geom FROM (select gid , nombre, tramo, geom from puntos) as foo
using
unique geom
#DATA geom FROM (select * from puntos) as foo using unique gid
Or there is some paramter in the configuration files of mapserver or php that
I
have to change.
There is the code of the function:
function CercaPunto($point,$map,$radius) {
$qlayer = $map-getLayerByName('trazado');
$qlayer-set(tolerance,$radius);
@$qlayer-queryByPoint($point, MS_MULTIPLE, $radius);
$numResults = $qlayer-getNumResults();
if ($numResults != 0) {
for ($i = 0; $i $numResults; $i++) {
$query_result = $qlayer-getResult($i);
$Lista_ele[$i] = $query_result-shapeindex; // Here is the error
with postgres
}
} else {
$valido =0;
$Lista_ele = ;
}
return $Lista_ele;
} // end CercaPunto
Thank you very much.
Guillermo Tamburini
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
- Fin del mensaje reenviado -
___
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] Dot Density idea
Martin, Assuming you wanted feedback - from an epidemiologists perspective, I like the first one - Complete Spatial Randomness is an important concept to avoid engendering a perception of order or clustering, and I'm thinking as long as the relative 'dot-density' is correct for each polygon, the CSR approach is less like to lead people to think the dots are located on significant points - in the context of obscuring actual locations etc this is important. If you want it more regular, then maybe you don't need to randomise it - or maybe this just means the function needs a user parameter to be able to set the level of randomisation - From 0 (completely ordered) to 1 (completely Random) (... to 2.5 completely clustered?) Just my thoughts - also for my money, I wouldn't change dot sizes. very confusing. cheers Ben On 07/05/2010, at 6:11 , Martin Davis wrote: Check out this blog post for some images of different kinds of random point fields: http://lin-ear-th-inking.blogspot.com/2010/05/more-random-points-in-jts.html Martin Davis wrote: Sounds like it could work - with maybe a bit of fiddling to deal with cases where the grid cells overlapped the polygon only slightly? Random perturbation by cell radius can still result in some points being very close together. (And I think this would also be an issue where only a small part of each grid cell overlapped the polygon). This may or may not be desirable. Perhaps a further check could be made to reduce the radius for points where this occurs. Or maybe some sort of simulated annealing process could be use to push the points into a more even distribution. M Paul Ramsey wrote: Even-yet-random :) nice requirement. How about just starting with a regular grid and then perturbing the elements randomly with a radius of a cell size? You can use the area of the polygon and number of needed points to calculate the appropriate cell size and go from there. P On Thu, May 6, 2010 at 10:28 AM, Martin Davis mbda...@refractions.net wrote: Good point about the need for even distribution of the points. That seems like a whole lot harder to code than simply randomly placing points in a polygon. Does anyone have any pointers to algorithms for producing this effect? George Silva wrote: The really big problem with dot density is that dots can overlap themselves, masking the real number, so if anything will be developed in this area, the points should be A) evenly distributed or B) randomly distributed, but with some sort of colision tests, so there is no or little overlap. This is a interesting idea, especially if we could make a materialized view with those points, which could be added to GIS software for presentation. George On Thu, May 6, 2010 at 1:53 PM, Sufficool, Stanley ssuffic...@rov.sbcounty.gov wrote: Looks nasty, but it might work: select st_line_interpolate_point( st_intersection( the_geom, st_makeline( st_pointn(st_exteriorring(the_geom), (rand1.rand * st_npoints(st_exteriorring(the_geom)))::int), st_pointn(st_exteriorring(the_geom), (rand2.rand * st_npoints(st_exteriorring(the_geom)))::int) ) ) ,rand3.rand ) from insert_your_table_name_here, (select random() as rand, generate_series(1,1000) as point_number) as rand1 JOIN (select random() as rand, generate_series(1,1000) as point_number) as rand2 ON rand1.point_number = rand2.point_number JOIN (select random() as rand, generate_series(1,1000) as point_number) as rand3 ON rand2.point_number = rand3.point_number WHERE st_geometrytype( st_intersection( the_geom, st_makeline( st_pointn(st_exteriorring(the_geom), (rand1.rand * st_npoints(st_exteriorring(the_geom)))::int), st_pointn(st_exteriorring(the_geom), (rand2.rand * st_npoints(st_exteriorring(the_geom)))::int) ) ) ) = 'ST_LineString' AND oid = 5030 /* Enter your own OID here */ limit 100 -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Martin Davis Sent: Thursday, May 06, 2010 8:56 AM To: John Abraham; postgis-users@postgis.refractions.net; Martin Davis Subject: Re: [postgis-users] Dot Density idea I was thinking the same thing! strk wrote: ST_RandomPoinsOnSurface(geometry, numpoints) would be an interesting function indeed. Sounds like a good job for GEOS/JTS. --strk; On Mon, May 03, 2010 at 10:49:32PM -0600, John Abraham wrote: One of the things I miss about using ESRI's GIS is the ability to do dot-density maps. Within a polygon, the number of dots is proportional to a value, and the dots are randomly placed. I
Re: [postgis-users] OSM2pgsql, OpenStreetMap, MapServer, EPSG:900913, OpenLayers: issue
I think you need to ask this on the MapServer List - MapServer doesn't use the
postgis spatial_ref_sys table for it's projections, but the proj epsg library
files, so you need to put the definition in there - I googled MapServer
Spherical Mercator Projection 900913 to find out how to do this.
alternatively, you could just put the projection directly into the mapfile -
look at http://spatialreference.org/ref/sr-org/6/
and click on MapServer mapfile
PROJECTION
proj=merc
a=6378137
b=6378137
lat_ts=0.0
lon_0=0.0
x_0=0.0
y_0=0
k=1.0
units=m
nadgri...@null
wktext
no_defs
END
good luck.
cheers
Ben
On 06/05/2010, at 21:10 , roshni.spain wrote:
Hi, I have more or less the same problem than you. I am rendering OSM data
(imported to PostGIS with osm2pgsql) and i need to view the map with
OpenLayers on MapServer.
I am more or less on the same point than you, because when i changed on the
map file all the projection info to init=epsg:900913, the map goes blank.
It comes empty with no information. Plus, the mapserver does not return any
error in the log, and i really have no clue what i can do to fix this.
My OSM data table (planet_osm_line) is on epsg:900913 and this is what i
need to show on the map, but with no success. I have tried many options but
i can never go further than this.
Any clue??
Thank you
ibrahim saricicek wrote:
Hi;
1) is your osm data table in Epsg:4326 projection?
if so use; init=epsg:4326 for each layer..
2) 'grey empty image'?? so you can get map from mapserver?
right click on a tile and copy image location. Try the copied url, is
there
an error?
Regards..
On Tue, Mar 16, 2010 at 12:20 PM, Mulone1 mul...@rome.com wrote:
(apologies for cross-posting)
Hi everybody,
I'm trying to render openstreetmap data on a WMS on MapServer.
The system used to work with epsg:4326 but in our project we need a
GoogleMaps like projection and we chose epsg:900913.
These are the steps I followed to reimport everything for the new
projection:
1) download osm xml file from Cloudmade (e.g. Italy.osm.bz2)
2) Run 900913.sql on my PostGIS
3) import that file into PostGIS with command:
./osm2pgsql -U userid -W -H host -d maps -p myprefix -S default.style -c
-m
italy.osm.bz2
4) At this point everything seems ok. The geometry columns have 900913 as
SRID.
5) update MapFile with:
WEB
METADATA
wms_srs epsg:900913
...
PROJECTION
init=epsg:900913
END
Then in each layer I put:
PROJECTION init=epsg:900913 END
and all of the queries have: using srid=900913
6) load the wms layer into OpenLayers
with
var wms = new OpenLayers.Layer.WMS(OpenStreetMap, mainurl, {
...
units : 'm',
projection:new OpenLayers.Projection(EPSG:900913),
...
Unfortunately all I get is a grey empty image. Looking at the map server
log
I noticed that it's full of:
[Mon Mar 15 17:49:44 2010].517000 msPostGISLayerWhichShapes query status:
2
[Mon Mar 15 17:49:44 2010].517000 msPostGISLayerWhichShapes got 0 records
in
result.
[Mon Mar 15 17:49:44 2010].517000 msPostGISLayerNextShape called.
With the other projection I was getting the records.
Do you have any idea about what could be wrong in my set up? I tried to
debug it in several ways but I couldn't find anything wrong.
Cheers,
Mulone
--
View this message in context:
http://old.nabble.com/OSM2pgsql%2C-OpenStreetMap%2C-MapServer%2C-EPSG%3A900913%2C-OpenLayers%3A-issue-tp27915684p27915684.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
--
View this message in context:
http://old.nabble.com/OSM2pgsql%2C-OpenStreetMap%2C-MapServer%2C-EPSG%3A900913%2C-OpenLayers%3A-issue-tp27915684p28472339.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] Dot Density idea
I have used an R function before (using maptools package) to do something similar, but I can't remember the details, so I would be considering looking at PL/R. cheers Ben On 06/05/2010, at 15:40 , strk wrote: On Mon, May 03, 2010 at 10:49:32PM -0600, John Abraham wrote: One of the things I miss about using ESRI's GIS is the ability to do dot-density maps. Within a polygon, the number of dots is proportional to a value, and the dots are randomly placed. I find it useful to be able to present several data values at once (e.g. blue dots for population, red dots for employment). I also find that it is a more intuitive way of scaling for zone size than dividing the value by the area of the zone. That is, the count of the dots represents the actual number, but the density of the dots represents the density of the number. So I don't have to decide whether to divide the value by the area of the polygon to plot density: both the absolute number and the density are easily visible. Since my open-source GIS viewing systems (mostly QGIS and Mapserver) won't plot dot-density, I've done without. But today I realized that I can build these on the server instead. I can generate random points within the bounding-box of the polygon, throwing out those that aren't contained within the polygon, repeating until I have enough. Then I can save these points as a separate layer, and display this layer using almost any desktop or web based viewer! Has anyone done this? Can I do it in SQL or do I need to write something in PL/pgsql? PL/pgsql would be easier than SQL at the minimum. Still, a C implementation would likely be better, for speed reasons. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ 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 find the nearest road
Julian, I have been trying to modify the function to take a tablename when it is called, and although I can't see why it wouldn't work, it doesn't. This is the one that does work - pretty similar I think to the one online. I normally create a function to find the nearest geography of interest, and then feed that location (as geometry) into this function. In most cases I only want the start or finish node (for pgrouting) but sometimes it helps to have the record id for sorting out bad topography (like OSM data). There are people on the postgis list who have a much better handle on this stuff, so I am copying this back to that list as well. Good luck cheers Ben CREATE OR REPLACE FUNCTION find_nearest_road( point geometry, sf varchar(6), OUT value int ) AS $BODY$ DECLARE max_search_radius real := 5.0; -- working in degrees search_radius real := 0.01; rec record; BEGIN LOOP SELECT gid, source, target INTO rec FROM road m -- matches the table with the topography in it. WHERE st_expand(point, search_radius) m.the_geom ORDER BY st_distance(point, m.the_geom) LIMIT 1; IF FOUND THEN -- you could really simplify this, it might speed it up IF substring(sf from 1 for 1) iLIKE 's' --start or Source THEN value := rec.source; ELSIF substring(sf from 1 for 1) iLIKE 't' --target OR substring(sf from 1 for 1) iLIKE 'f' --finish THEN value := rec.target; ELSIF substring(sf from 1 for 1) iLIKE 'g' --gid OR substring(sf from 1 for 1) iLIKE 'i' --id THEN value := rec.gid; END IF; EXIT; END IF; search_radius := search_radius * 2.0; EXIT WHEN search_radius max_search_radius; END LOOP; END; $BODY$ LANGUAGE plpgsql STABLE STRICT; On 24/04/2010, at 5:02 , julian andres montes galvis wrote: Hi ben, I am julian from Colombia. googling in the web and found your postgres function find_nearest_road do you have a new version from this ? thanks and advanced Julian -- Julian Montes AZ LOGICA LTDA Telefax (+57 1) 800 1228 Carrera 50 # 103B - 15 Bogotá-Colombia ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] using dynamic tables in plpgsql nearest neighbour query
G'day all,
I am trying to sort out a routing problem over much of South-East Asia, and
trying to fill gaps using a number of different data sources.
I have a plpgsql function which works - with a static table name etc. When I
tried to change it to a dynamic name using the execute 'query' using variables;
statement, it doesn't work.
I am suspicious that the geometry type might be the cause
Anyways, this works : (using a multilinestring table with vertices assigned
using the pgrouting function.
CREATE OR REPLACE FUNCTION find_nearest_road(tabname varchar,
point geometry, sf varchar(6), OUT value int
) AS
$BODY$
DECLARE
max_search_radius real := 5.0; -- this is assuming working in degrees I
guess!
search_radius real := 0.01;
rec record; -- this has to match your lookup table
BEGIN
LOOP
SELECT gid, source, target INTO rec
FROM road m -- and you might want to change this
WHERE st_expand(point, search_radius) m.the_geom
ORDER BY st_distance(point, m.the_geom)
LIMIT 1;
IF FOUND THEN -- you could really simplify this, it might speed it up, but
I kind of like it.
IF substring(sf from 1 for 1) iLIKE 's' --start or Source
THEN
value := rec.source;
ELSIF substring(sf from 1 for 1) iLIKE 't' --target
OR substring(sf from 1 for 1) iLIKE 'f' --finish
THEN
value := rec.target;
ELSIF substring(sf from 1 for 1) iLIKE 'g' --gid
OR substring(sf from 1 for 1) iLIKE 'i' --id
THEN
value := rec.gid;
END IF;
EXIT;
END IF;
search_radius := search_radius * 2.0;
EXIT WHEN search_radius max_search_radius;
END LOOP;
END;
$BODY$ LANGUAGE plpgsql STABLE STRICT;
and this doesn't :- the first 5 lines after loop above have been replaced with
the 7 lines below, but I haven't even quoted the tablename - it's still
hardcoded.
CREATE OR REPLACE FUNCTION find_nearest_road(tabname varchar,
point geometry, sf varchar(6), OUT value int
) AS
$BODY$
DECLARE
max_search_radius real := 5.0; -- this is assuming working in degrees I
guess!
search_radius real := 0.01;
rec record; -- this has to match your lookup table
BEGIN
LOOP
EXECUTE 'SELECT gid, source, target
FROM road m -- and you might want to change this
WHERE st_expand($1, $2) m.the_geom
ORDER BY st_distance($1, m.the_geom)
LIMIT 1'
INTO rec
USING point, search_radius;
IF FOUND THEN -- you could really simplify this, it might speed it up, but
I kind of like it.
IF substring(sf from 1 for 1) iLIKE 's' --start or Source
THEN
value := rec.source;
ELSIF substring(sf from 1 for 1) iLIKE 't' --target
OR substring(sf from 1 for 1) iLIKE 'f' --finish
THEN
value := rec.target;
ELSIF substring(sf from 1 for 1) iLIKE 'g' --gid
OR substring(sf from 1 for 1) iLIKE 'i' --id
THEN
value := rec.gid;
END IF;
EXIT;
END IF;
search_radius := search_radius * 2.0;
EXIT WHEN search_radius max_search_radius;
END LOOP;
END;
$BODY$ LANGUAGE plpgsql VOLATILE STRICT;
the first function returns :
prices=# select
find_nearest_road('road','010120E61052FC3DCF94A459409734BBCFC2243240'::geometry,'s');
find_nearest_road
---
1507
(1 row)
and the second version of the function :
prices=# select
find_nearest_road('road','010120E61052FC3DCF94A459409734BBCFC2243240'::geometry,'s');
find_nearest_road
---
(1 row)
So I guess it comes down to what's wrong between :
SELECT gid, source, target INTO rec FROM road m WHERE st_expand(point,
search_radius) m.the_geom ORDER BY st_distance(point, m.the_geom) LIMIT 1;
EXECUTE
'SELECT gid, source, target FROM road m WHERE st_expand($1, $2) m.the_geom
ORDER BY st_distance($1, m.the_geom) LIMIT 1'
INTO rec USING point, search_radius;
Any advice gratefully received,
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Buffer questions
To look up an appropriate projection, you have a number of options: Easy : http://www.spatialreference.org/ Sort of easy : SELECT srid, srtext FROM spatial_ref_sys WHERE srtext LIKE '%GDA94%'; -- To find something containing Geocentric Datum of Australia 1994 But Australia is a big country, so it depends where in Australia you are - and how accurate you want to be. In Western Australia you might get away with just using epsg:28350, but you could choose better for Macquarie Island 28356? If you are going globally you might want to just use normal UTM Zones (WGS84). The South ones all start with 327_ _... ie Western Australia would be 32750. The North ones start with 326_ _. Good luck - look at the website above. cheers Ben On 15/04/2010, at 14:15 , Chen, Li [Contractor] wrote: Hi, Do you might know a proper appropriate_projection_epsg? I am in Australia but I might need to use data from the whole world. Also, I was not able to find a SRID for ECEF reference system, does anyone know? Thanks, Li -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Thursday, 1 April 2010 4:42 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] ST_Buffer questions G'day Li, I can't help with Q2, but On 01/04/2010, at 12:28 , Chen, Li [Contractor] wrote: Q1. ST_Buffer(g1, range) is able to return a geometry within the range of g1. So, I define two point using lon/lat (SRID=4326) and range 10km. I want to see whether they cross each other by using ST_Crosses(g1, g2). However, I don't know the unit of the range parameter in ST_Buffer(g1,range) as it is not provide in the docs. So is it km or meters? The same unit as your Geometry - decimal degrees. Obviously due to the change in the value of this unit at differing latitudes, this is not useful, so a more sensible approach is either to transform your point into a projection using metres, and then use metres (off the top of my head it would look like : select st_buffer(st_transform(g1, appropriate_projection_epsg),1); but you should check the docs) or use the geography type from postgis 1.5? but I haven't tried it yet? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to backup/restore
Nicholas, I can't answer 'What's the right way?', as I'm sure I don't have it yet, but I have found the biggest issue is in getting postgis to transfer, so I do it in steps: 1. always install postgis into it's own schema (either create the schema - gis - and set the search-path before \i postgis.sql command, or edit the postgis.sql file) 2. when dumping, ignore the gis schema = pg-dump -N gis database database.dump 3. when recreating, create new database, import postgis (as in 1) then restore database.dump (psql newdatabase database.dump) Or some variant of the above - and I'd love to know / be shown a better way! As a side benefit (If it helps,) I also use the gis schema for reasonably static GIS data - background maps etc. Then my backups don't include it, and are often much smaller for it, which helps going between machines. cheers Ben On 09/04/2010, at 21:20 , Nicholas Bower wrote: I'm trying to create a new instance on a different platform of a postgis-enabled database, starting with just the schema definition. What's the right way? This doesn't work below - do I have to partition into separate schemas to have this work perhaps? Thanks, Nick [Solaris Postgis 1.3.4] pg_dump -scFc database schema.sql [Windows Postgis 1.5.1] pgrestore -d database schema.sql End result - lots of errors and missing any tables containing postgis objects. Note the hard coded c language function library paths below; pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 6; 2615 18201 SCHEMA wastac wastacad pg_restore: [archiver (db)] could not execute query: ERROR: schema wastac already exists Command was: CREATE SCHEMA wastac; pg_restore: [archiver (db)] Error from TOC entry 1223; 2612 17398 PROCEDURAL LANGUAGE plpgsql postgres pg_restore: [archiver (db)] could not execute query: ERROR: language plpgsql already exists Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 1115; 0 0 SHELL TYPE box2d postgres pg_restore: [archiver (db)] could not execute query: ERROR: type box2d already exists Command was: CREATE TYPE box2d; pg_restore: [archiver (db)] Error from TOC entry 216; 1255 17477 FUNCTION st_box2d_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_in' LAN... pg_restore: [archiver (db)] could not execute query: ERROR: function public.st_box2d_in(cstring) does not exist Command was: ALTER FUNCTION public.st_box2d_in(cstring) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 218; 1255 17479 FUNCTION st_box2d_out(box2d) postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION st_box2d_out(box2d) RETURNS cstring AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_out' L... pg_restore: [archiver (db)] could not execute query: ERROR: function public.st_box2d_out(box2d) does not exist Command was: ALTER FUNCTION public.st_box2d_out(box2d) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 1114; 1247 17475 TYPE box2d postgres ... pg_restore: [archiver (db)] could not execute query: ERROR: relation t_tile_geometry does not exist Command was: GRANT SELECT ON TABLE t_tile_geometry TO wastacportal; pg_restore: [archiver (db)] could not execute query: ERROR: relation t_tile_geometry does not exist Command was: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE t_tile_geometry TO wastac; WARNING: errors ignored on restore: 1586 ___ 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 backup/restore
OK On 12/04/2010, at 10:03 , Nicholas Bower wrote: 2. Dump just this separate data schema using pg_dump -Fc -N schema I think here you mean -n?, but it's six of one and half a dozen of the other. I routinely use different schema's for different aspects of the database, hence easier to just exclude one. I note your solution of the separate schema using default path hack - interesting that this works (assume you change the search path for all db updater roles). I'm not sure what you mean by this question, sorry. I do change the search_path for the database - ALTER database SET search_path TO data, reference, users, gis; if that is what you are referring to? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to backup/restore
Nicholas, On 12/04/2010, at 12:33 , Nicholas Bower wrote: On Mon, Apr 12, 2010 at 12:21 PM, Ben Madin li...@remoteinformation.com.au wrote: OK On 12/04/2010, at 10:03 , Nicholas Bower wrote: 2. Dump just this separate data schema using pg_dump -Fc -N schema I think here you mean -n?, but it's six of one and half a dozen of the other. I routinely use different schema's for different aspects of the database, hence easier to just exclude one. Yes -n you're right. I note your solution of the separate schema using default path hack - interesting that this works (assume you change the search path for all db updater roles). I'm not sure what you mean by this question, sorry. I do change the search_path for the database - ALTER database SET search_path TO data, reference, users, gis; if that is what you are referring to? Yep that's it - you're changing the search path not just of the restore, but all roles using that database ongoing so they can find the postgis functions. When I started experimenting with Postgis back in 2003, I couldn't get it to work so ever since I've used public schema for postgis. I should have tried harder ;) Yeah, I'm a relative newcomer to this, which may have saved me some grief, although I have once deleted the geometry type from a big database. Btw have you restored your backups from scratch before and found them to work? Yes - pretty routinely when I dump out the database to make a local copy, but also for upgrades to PostgreSQL (most recently to 8.4). I'm actually a bit of a tech luddite, and haven't ever mastered the more technical aspects of pg_dump and pg_restore, I just dump as a script (also using the -O flag normally), tar, rsync and then run the file into psql - since much of it is on remote servers I never really learnt how to use pgadmin. I've also done it when I have deleted the Geometry type from a big database... cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] outgrowing the standard Pg install
G'day Puneet, I'm no expert, but we have just started looking at using tablespaces to spread a database across multiple drives. You may already be across this. http://www.postgresql.org/docs/8.4/interactive/manage-ag-tablespaces.html I haven't yet worked out if you can spread one table across multiple drives though. Also, I'm not sure if it works on windows? cheers Ben On 06/04/2010, at 14:21 , P Kishor wrote: If my database grows beyond what is available on PostGIS/Pg computer, what are my options? Can I add more drives to the computer and have part of the database on one disk and another part on another disk? Or, do I have to plan ahead, chop up my database into disk-sized chunks, and install each chunk on separate machines? Here is an illustration -- computer 1: Single disk, 1 TB space available, PGDATA in /usr/local/pgsql/data database is going to 5 TB eventually. I start on computer 1, when that is filling up, I add external disks, so Postgres can just write spillover from /usr/local/pgsql/data to /external/pgsql/data (is this even possible?) OR computer 1: 1 TB space computer 2: 1 TB space .. and so on I divide my database into 1 TB chunks, install five instances of PostGres/PostGIS on the five computers (what a nightmare) -- Puneet Kishor ___ 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] splitting a PostGIS db across multiple disks
Puneet I'd wonder if a view can do it off multiple tables ? Having said that, you might want to check the archives for inheritance. I recall someone ?Leo? mentioning something similar when I was talking about keeping an archive table without the current data, but being able to look up data from both as a way of dealing with Change of Support issues. I think you can have an inherited table with no data inheriting from a number of tables. You can stop the query searching all tables by having constraints on them - maybe they could reflect bounding boxes? cheers Ben On 06/04/2010, at 22:33 , P Kishor wrote: I asked this question yesterday, and received a very helpful pointer from Ben Madin re. TABLESPACES. As noted in my reply in that thread, I am also investigating the possibility of splitting a single table across multiple disks. However, I am going to post this question in a different way in this new thread. Suppose I have a table FOO0 that stores info about every state in the union. I know that some of these states will have mongo number of rows, but I don't have to build all the states immediately. So, I start with a few states' worth data, putting it in the default /usr/local/pgsql/data location. Then I start outgrowing that disk, and need to add another state, so I add another disk, create a new tablespace, and create a new table called FOO1 in this new tablespace. Then I can store the new states in FOO1. As long as I break up my table into FOO0, FOO1, FOO2, and so on, I can store each FOOn in a new tablespace. And, as long as I ensure that each FOOn table contains a geographically consistent spatial extent, I can build logic in my application to query the correct table. So, lets say 0 lon to -10 lon data are stored in FOO0, and -10 lon to -20 lon in FOO1, then if the user requests data for -5 lon to -15 lon, I will have to query both FOO0 and FOO1. More work for me, but it is doable, no? Any insights on how to handle something like this? A corollary question -- are their any speed advantages to actually creating multiple PostGIS instances, perhaps even splitting them across multiple machines? Of course, it is going to be a pain in the ass for me to maintain more than one instance of PostGres/PostGIS, so I am not thrilled at that possibility. I'd rather have a single instance just be managing data across multiple locations as required. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ 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_Buffer questions
G'day Li, I can't help with Q2, but On 01/04/2010, at 12:28 , Chen, Li [Contractor] wrote: Q1. ST_Buffer(g1, range) is able to return a geometry within the range of g1. So, I define two point using lon/lat (SRID=4326) and range 10km. I want to see whether they cross each other by using ST_Crosses(g1, g2). However, I don’t know the unit of the range parameter in ST_Buffer(g1,range) as it is not provide in the docs. So is it km or meters? The same unit as your Geometry - decimal degrees. Obviously due to the change in the value of this unit at differing latitudes, this is not useful, so a more sensible approach is either to transform your point into a projection using metres, and then use metres (off the top of my head it would look like : select st_buffer(st_transform(g1, appropriate_projection_epsg),1); but you should check the docs) or use the geography type from postgis 1.5? but I haven't tried it yet? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Changing transaction behaviour in shp2pgsql
G'day all,
I'm using shp2pgsql to import a road network, of which the fine detail is not
so important.
Mac OS X 10.6.2 POSTGIS=1.4.1 GEOS=3.2.0-CAPI-1.6.0 PROJ=Rel. 4.7.1, 23
September 2009 USE_STATS (thanks William K)
$ shp2pgsql -s 28350 /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp
gis.mrwa_gda94 | psql australia
During the import a number of times (4) I get the
ERROR: invalid byte sequence for encoding UTF8: 0xec5343
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.
ERROR: current transaction is aborted, commands ignored until end of
transaction block
and the next several hundred records are skipped.
When I put the -W UTF8 flag in , I miss the errors but die when I get to about
101000 (out of 15) records in :
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR: syntax error at end of input
LINE 1: ...e_geom) VALUES ('1.1557400e+005','GILMORE ST (SCADDAN)',
in the event I can't work out the encoding, is there any way to turn off the
transaction behaviour, ie I'm happy to miss 4 records, but not 1000 as a result
of the transactions being aborted. (short of putting it all into a text file
and removing all the begin and commit commands...)
cheers
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Changing transaction behaviour in shp2pgsql - summary
Thanks all for the feedback - to summarise : shp2pgsql - Currently, all output is 'chunked' transactions - changing this behaviour not currently supported. - A patch could be provided to shp2pgsql to do this. - The suggestion is a -T switch with options such as single, chunked, no transactions. - I'm happy to look at submitting a patch... but it won't be until after I've submitted my thesis ... The text editor approach is to direct output into a file and find / replace the BEGIN / COMMIT lines. The on-the-fly alternative is to use a pattern matching program, with two solutions offered : Using sed (thanks Brent) shp2pgsql -s 28350 -W UTF8 \ /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp \ gis.mrwa_gda94 | sed 's/BEGIN//' | sed 's/COMMIT//' psql australia Using grep (thanks Steve) shp2pgsql ... | grep -v BEGIN | grep -v COMMIT | psql australia cheers Ben On 15/03/2010, at 4:35 , strk wrote: On Sun, Mar 14, 2010 at 04:58:02PM +0700, Ben Madin wrote: in the event I can't work out the encoding, is there any way to turn off the transaction behaviour, ie I'm happy to miss 4 records, but not 1000 as a result of the transactions being aborted. (short of putting it all into a text file and removing all the begin and commit commands...) Feel like working on a patch to make transaction policy selectable with a switch ? -T policy transaction policy (single*,none) Current policy is chunked, dunno for what rationale.. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ 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] Db connect
Ravi, A little bit more information about what is happening would help? Do you have some output from the apache/php error log? Does the User / Password combination have access to the tables normally? cheers Ben On 03/03/2010, at 11:01 , Ravi wrote: Hi, am using OSGeo4W bundle and the apache and php there in. Apache installed OK showing as aservice and in the browser http:\\localhost issues the OSGeo4w welcome screen and phpthere on with the following wish to connect to PGSQL 8.4 and postGIS there in with the following. But it doesnt work. Where am I going wrong ? $db_handle = pg_connect(”host = localhost port = 5432 dbname = postgis user = postgres password = GrassJump”); $query = “SELECT * FROM rjybound”; $result = pgexec($dbhandle, $query); Ravi Kumar Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/ ___ 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
