Re: [postgis-users] pgsql2shp encoding problem
Thanks strk, On 05/07/2012, at 5:07 PM, Sandro Santilli wrote: > On Thu, Jul 05, 2012 at 01:39:16PM +0800, Ben Madin wrote: >> 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. > > Does their system support utf8 ? I don't know (but I assume so - Windows Server 2008 R2 SP1 64 bit) >> Can anyone advise me if this is true and reasonable? Or more importantly, >> will work with ArcGIS and / or MapServer? > > You tell us what works with what by testing it. I don't have (nor do I have much inclination to invest in…) the above system (I don't really have any Windows background, and I'm not involved in the software, only supply the spatial data in shapefile format - not my choice. They are using MapServer 5.2.1 (on IIS 7.5, which has only caused me grief in the past), hence my desire to see if anyone else had experience before I go backwards down that line. > My impression is that the standard should be UTF8 for everything, > specifically to avoid the localisation issue once and for all. 'should be' … it's a world away from 'is' - there doesn't even seem to be a standard for referring to the standards! i.e. LATIN1 ≈ ISO-8859-1 ≈ 28591≈ ISO88591 and UTF8 ≈ utf-8 cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[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] 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 wrote: > > From: Ben Madin > Subject: [postgis-users] Size of a multipolygon > To: "PostGIS Users Discussion" > 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
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
[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] Help please with PL/R
Gery, You have created two loess functions, one which accepts two arrays of doubles, and one which accepts a geometry and a double precision number. You have two error messages : On 10/04/2012, at 1:31 AM, Gery . wrote: > CREATE FUNCTION > psql:smoothingtest.sql:51: ERROR: function loess(double precision[]) does > not exist > LINE 8:SELECT loess(array_agg(st_x(point))) AS x, loess(array_ag... > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. There is no function that I can see that accepts a single list of double values… you might want to count your brackets..! > psql:smoothingtest.sql:53: ERROR: function loess(geometry) does not exist > LINE 1: select astext(loess(geom)) from chanaral_rawnav_point_wgs84; > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. There is no function that I can see that accepts a geometry only… you have defined one that accepts geometry and a double precision number. HTH 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] 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
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] 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] 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] 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] 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 Kishor 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
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
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
[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] 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] 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] 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] Undefined symbol "list_length" ? postgis 1.5.3 from FreeBSD ports
Thanks Sandro, I can only find one installation of postgres (in /usr/local/bin/postgres) There are two clusters running on the machine (and have been for years), and it was upgraded to 9.0 a couple of months ago, and postgis was 1.5.3, but I can't install it. (interestingly, I haven't be able to reinstall pl/r either, which is driving me insane) I'm reinstalling postgres and postgis from scratch (using ports), so I'll let you know what I find 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
[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] 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 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] 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
[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] 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
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] pgsql2shp
Raffaele
Have you considered using gdal to directly bring the data into R :
using something like :
library(rgdal)
mymap <- readOGR("PG:dbname=mydbname user=mydbuser password=mydbpassword",
"map")
cheers
Ben
On 26/09/2011, at 4:42 PM, Raffaele Morelli wrote:
> Hi,
>
> I am working with R and using pgsql2shp to export shape files from pg. The
> shapes are created by pgsql2shp but when I try to import them in a R
> enviromnent I get this error
>
> Error in res[i, ] <- Map$Shapes[[i]]$verts : replacement has length zero
>
> I can load the shape files in Qgis but not in R. Moreover, if I load one of
> those files in Qgis and then export it again... this file is correctly loaded
> by R.
>
> I really cant' guess what's wrong with pgsql2shp. Any ideas?
>
> Regards
> Raffaele
>
> --
> L'unica speranza di catarsi, ammesso che ne esista una, resta affidata
> all'istinto di ribellione, alla rivolta non isterilita in progetti, alla
> protesta violenta e viscerale.
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://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 with 4 parameters does not use spatial index
Markus, I'm only speculating, but the index is managed in the units of long / lat, and as they are not directly related to metres, in order to use the index it would have to transform all the index values on the fly, and either it can't / doesn't know how (therefore doesn't bother), or in the eyes of the query planner this is more expensive than just a full table scan. Maybe attaching the results of the explain would help others wiser than I am? cheers Ben On 25/09/2011, at 11:25 PM, Markus Innerebner wrote: > HI all > > I migrated to postgis 1.5.3 in order to use the st_dwithin function with > 4 parameters that supports meters as unit shown as Q1, while query Q2 is > the old function, that does not support meters for the projection format > 4326. > > The problem is, that query Q1 does not use the spatial index, while Q2 > does it. Any idea what it could be?? > > Thanks for your answer > > Markus > > --- > Q1: >SELECT NT.id >FROM > it_nodes N, > it_nodes NT >WHERE > N.ID='22' AND ST_DWITHIN(N.GEOMETRY,NT.GEOMETRY,500,true)=true > > Q2: >SELECT NT.id >FROM > it_nodes N, > it_nodes NT >WHERE > N.ID='22' AND ST_DWITHIN(N.GEOMETRY,NT.GEOMETRY,0.005)=true > > > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://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] BMP2Postgis
Bob, do you want Vector or Raster files ultimately? Raster, I think you could convert to tiff and use grass to geolocate them? maybe even a plugin exists for qgis. Vector? you could try grass to convert the raster? cheers Ben On 22/09/2011, at 2:11 PM, Bob Pawley wrote: > Is it at all possible to convert bitmaps to postgis? > > If so, is there an application available? I only need to convert a hundred or > so images so I don’t want to spend a whole lot of money – none preferably. > > Bob > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] 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] Longitude and latitude ranges
Jaime, On 23/08/2011, at 7:22 AM, Jaime Casanova wrote: > after some tries, i haven't managed to make this query use the GiST > index that was created on columns transmitter_mv.punto nor > rowlatlong.punto and it finishes using a seq scan on table > transmitter_mv for every row in rowfreqlevel that satisfies the join > and where conditions. > > Stephen Woodbridge, made me notice in > http://postgis.refractions.net/pipermail/postgis-users/2011-August/030575.html > that in the plan the POINT is being casted to geography so i decided > to bite the bullet and use geography columns intead but when i tried > to create the points from long/lat pairs i got this error (which i > didn't get when the column was geometry) > """ > db=# update transmitter_mv set punto = st_makepoint(tx_long, tx_lat); > ERROR: Coordinate values are out of range [-180 -90, 180 90] for GEOGRAPHY > type > """ > > any ideas why this is happening? It may help... it may of course not ... to set the SRID of the Geometry when you make the point, vis > db=# update transmitter_mv set punto = st_setSRID(st_makepoint(tx_long, > tx_lat), mysrid); where mysrid matches the projection you selected for the column punto ?4326? It would really help you if you are getting few responses to put a short repeatable example of your process and a subset of data that causes the problem. You can probably put few enough points to make it unidentifiable, or even shift them (as long as you are still getting the problem) if you are worried about privacy issues. cheers Ben ___ 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] 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
[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] 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
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 > -- > > > my postgis map > > > > > > > > > > src=/cgi-bin/mapserv.exe?map=C:\OSGeo4W/apps/mapserver-tutorial/mytests/example.map&mode=map > border=1> > > > > > > > > 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] Difficulty importing example file nyc_buildings.sql
Bruce,
a couple of things :
$PATH won't help - the only program you are using is psql, and that is running.
FWIW, I would put /usr/local/pgsql-9/0/bin into your path so you don't have to
type the full command every time.
AddGeometryColumn is a PostGIS function - if you can run
psql nyc
and from within psql try :
SELECT postgis_full_version();
that will provide a lot of information - if it works, and tells you something
about postgis, put it here. I think someone else suggested looking to see if
the function exists.
The second one is the database search_path. within the database you can have
schema's, which are like subsets of the database. many people only ever use the
default one (called 'public') but it is possible that you have more, or the
dumped data has more. Just having a schema is no guarantee of being able to
access it - you need to set search_path to whatever to access it. You can alter
database nyc set search_path.
I think to be much more help, a reproducible example is going to be required,
but they can be hard to produce, so you might need to at least provide the
output of these commands from within psql:
\dn
\dt
SHOW search_path;
SELECT postgis_full_version();
and maybe the nyc_buildings.sql file, or the first 50 lines of it.
cheers
Ben
On 17/08/2011, at 11:33 PM, b...@brucecallander.com wrote:
> Thanks to Ben Madin for advice. I removed the apostrophes from the SRID in
> nyc_buildings.sql (using TextEdit) and re-ran the command
>
> /usr/local/pgsql-9.0/bin/psql -f /Users/bacmac/nyc_buildings.sql nyc
>
> Unfortunately it produced the same result:
>
> BEGIN
> psql:/Users/bacmac/nyc_buildings.sql:3: NOTICE: CREATE TABLE will create
> implicit sequence "nyc_buildings_gid_seq" for serial column
> "nyc_buildings.gid"
> psql:/Users/bacmac/nyc_buildings.sql:3: NOTICE: CREATE TABLE / PRIMARY KEY
> will create implicit index "nyc_buildings_pkey" for table "nyc_buildings"
> CREATE TABLE
> psql:/Users/bacmac/nyc_buildings.sql:4: ERROR: function
> addgeometrycolumn(unknown, unknown, unknown, integer, 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 wonder if I need to adjust $PATH so that the function AddGeometryColumn is
> found? Even when I change directory to /usr/local/pgsql-9.0/bin my Mac cannot
> find psql if I type that command on its own. I always have to use the full
> pathname /usr/local/pgsql-9.0/bin/psql. Strange, because typing 'ls' after
> changing the directory brings up the complete list of files including psql.
>
> Bruce Callander
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://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
> Subject: [postgis-users] ERROR: ptarray_area_spheroid: cannot handle
>ptarraythat crosses equator
> To: postgis-users@postgis.refractions.net
> Message-ID:
>
> 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 0,15.856
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] 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)
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 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
[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] 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] Noob request - using ST_SimplifyPreserveTopology() on Tiger Data
Christian,
My guess here is that because you've told the geometry to be a MULTIPOLYGON,
but you may have returned a POLYGON, you have indeed violated the check
constraint on the geometry type.
try using st_multi() to get around it, as in :
> update public.tl_2010_us_cd111 set simple_geom =
> ST_Multi(ST_SimplifyPreserveTopology(the_geom, 15));
cheers
Ben
On 28/06/2011, at 11:23 PM, Christian Guirreri wrote:
> I get the following error:
> ERROR: new row for relation "tl_2010_us_cd111" violates check constraint
> "enforce_geotype_simple_geom"
> SQL state: 23514
>
> Here's my SQL:
> select addGeometryColumn('public', 'tl_2010_us_cd111', 'simple_geom', 4326,
> 'MULTIPOLYGON', 2);
> update public.tl_2010_us_cd111 set simple_geom =
> ST_SimplifyPreserveTopology(the_geom, 15);
>
>
> On Mon, Jun 27, 2011 at 2:07 PM, Stephen Woodbridge
> wrote:
> On 6/27/2011 1:37 PM, Christian Guirreri wrote:
> I'm trying to do something fairly simple. I'd like to take the Tiger
> 2010 Data - tl_2010_us_cd111 and tl_2010_us_county10 - and create shape
> files that are considerably smaller and more simplified. In the past
> I've just used MapShaper and been OK with that, but I was having trouble
> uploading tl_2010_us_county10 into it and I've decided its time to learn
> some GIS basics anyway.
>
> I've definitely successfully setup my environment - PostegreSQL 9 with
> PostGIS. I've used the shp2pgsql wizard included with pgadmin to import
> the data sets into their own databases, without the "Load Geography
> column" checked. I've been able to export this data back to shp
> using pgsql2shp and view the resulting shp in QuantumGIS and ACDSee Canvas.
>
> But when it comes to simplification - I'm not sure where to even
> start. I'm no database expert beyond some simple mysql so GIS is
> especially overwhelming. I only know that I want to
> use ST_SimplifyPreserveTopology() as I'm fairly certain a number of
> things will disappear if I don't.
>
> Would love if someone could provide me with the SQL to make this happen!
>
> First you should understand that ST_SimplifyPreserveTopology() works on a
> single geometry at a time and not on multiple geometries in a coverage. The
> difference is in a coverage the the polygons that share a common edge that is
> simplified would continue to have a common simplified edge. When polygons are
> simplified individually with knowledge of the adjacent polygons it is
> possible for gaps and overlaps to occur along the simplified edge.
> ST_SimplifyPreserveTopology() is designed to try and minimize these effects
> but there are no guarantees.
>
> You might try something like:
>
> select addGeometryColum('myschema', 'mytable', 'simple_geom', 4326,
> 'MULTIPOLYGON', 2);
> update myschema.mytable set simple_geom =
> ST_SimplifyPreserveTopology(the_geom, );
>
> where is replaces by an appropriate value. This will depend on
> the some trial and error. The above assume srid=4326 which is probably not a
> good projection for doing simplification so you might want to project your
> data into some other UTM or Mercator projection depending on your coverage
> area and tolerance needs to be set with respect to those units.
>
> -Steve W
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
___
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_myblog&task=view&id=277&Itemid=59&lang=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()
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] 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
[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
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, 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
__
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 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 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 wrote: >> >> From: Ben Madin >> Subject: Re: [postgis-users] how to keep geometry_columns in sync with >> tables and views >> To: "PostGIS Users Discussion" >> Date: Thursday, May 19, 2011, 12:50 PM >> >> Ge, >> >> Try >> >> SELECT Populate_Geometry_Columns(); >> >> http://postgis.refractions.net/docs/Populate_
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 wrote: > > From: Ben Madin > Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables > and views > To: "PostGIS Users Discussion" > 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 wrote: >> >> From: Edward Mac Gillavry >> 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] 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 wrote: > > From: Edward Mac Gillavry > 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] Update column to nearest neighbor's value using subselect
Pete, I'm not very good at these, but I don't think you have specified your columns enough in the subselect. There isn't any relationship between this query > SELECT b.tornado_index > FROM working.zip_tornado_index b > WHERE b.tornado_index IS NOT NULL > AND ST_DWithin(the_geom,b.the_geom,0.1) > ORDER BY ST_Distance(the_geom,b.the_geom) ASC NULLS LAST > LIMIT 1 and the insert. Does this return the same value every time? By this I mean, you have the_geom in your subselect, but no reference to a table outside the subselect. If you run this query, > UPDATE working.zip_tornado_index z > SET tornado_index = ( > SELECT b.tornado_index > FROM working.zip_tornado_index b > WHERE b.tornado_index IS NOT NULL > AND ST_DWithin(z.the_geom,b.the_geom,0.1) > ORDER BY ST_Distance(z.the_geom,b.the_geom) ASC NULLS LAST > LIMIT 1 > ) > WHERE tornado_index IS NULL; is the result any different? Other's can probably help more than I. cheers Ben On 14/05/2011, at 5:13 AM, Pete Yunker wrote: > I have a table containing tornado index values by zip code. A small number > of the zip codes do not have values. I would like to update the > tornado_index values for those records by simply using the value for the > nearest zip_code (by comparing distance to the centroid) in the same table. > The UPDATE statement that I use executes, but it appears to return the same > record from the sub-select each time. Doesn't the sub-select get executed > for each record, as it depends on a value (the_geom) from the outer table? > Is there a better way to do this? > > -- Using postgres 8.3.6 and postgis 1.3 > > -- working.zip_tornado_index -- > idint > zip_code text > tornado_index int > the_geom geometry (SRID=4326) > > > UPDATE working.zip_tornado_index > SET tornado_index = ( > SELECT b.tornado_index > FROM working.zip_tornado_index b > WHERE b.tornado_index IS NOT NULL > AND ST_DWithin(the_geom,b.the_geom,0.1) > ORDER BY ST_Distance(the_geom,b.the_geom) ASC NULLS LAST > LIMIT 1 > ) > WHERE tornado_index IS NULL > ; > > > Thanks, > Pete > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://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] PostGIS Error: Loading with SPIT-Plugin-> MULTINULL
Florian,
I've never used SPIT, but I'm sure you can see that there is no geometry type
in postgis known as MULTINULL. I suspect you might be better to follow this up
with the qgis mailing lists.
Does it work using shp2pgsql?
As a second observation, you are using what I think would now be regarded as
quite old builds of all the architecture - there may well have been bugs that
are long since fixed. If you can try on a more modern set of tools, you might
have more luck.
good luck.
cheers
Ben
On 10/05/2011, at 4:19 PM, Florian Reimer wrote:
> Hello,
>
> we have a large Multipolygon Shapefile and try to load it from QGIS into
> PostGIS via the SPIT-Plugin. The file is recognized only as MULTINULL while
> it should be MULTIPOLYGON.
>
>
> SELECT AddGeometryColumn('public','PA-Prop','the_geom',32617,'MULTINULL',2)
>
> ERROR: Invalid type name - valid ones are:
> GEOMETRY, GEOMETRYCOLLECTION, POINT,
> MULTIPOINT, POLYGON, MULTIPOLYGON,
> LINESTRING, MULTILINESTRING,
> GEOMETRYCOLLECTIONM, POINTM,
> MULTIPOINTM, POLYGONM, MULTIPOLYGONM,
> LINESTRINGM, or MULTILINESTRINGM
> CONTEXT: SQL statement "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 , $5 ,
> $6 )"
> PL/pgSQL function "addgeometrycolumn" line 4 at select into variables
>
> There might be some error with Geometry, though the file works fine in QGIS
> and ArcGIS. Anyone had this problem before and might know something about it?
>
> We are using:
>
> PostGIS
> Library: 1.1.6
> Scripts: 1.1.6
> GEOS: 3.0.0-CAPI-1.4.1
> Proj: Rel. 4.6.0, 21 Dec 2007
>
> PostgreSQL 8.1.19 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
> 20061115 (prerelease) (Debian 4.1.1-21)
>
> Thank you very much!
> Florian
>
>
> --
> Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
> belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://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 -> Mapserver white background
Graham, It looks OK to me. Although the brevity is good, it might help if you could also attach the table definition - is it just producing a blank png image? is it in the same projection etc...? How are you accessing it (ie through WMS, mapscript or standard mapserver output). I've also copied this back to MapServer list... cheers Ben On 27/04/2011, at 3:26 PM, Graham Walker wrote: > Hi all, fairly new to all this. I was wondering if you could help me > with the following issue. I can't seem to display my postgis table in > mapserver. I can display shapefiles and html templates fine, but when > I try to connect to my postgis table I simply get a white background > (or whatever colour I set it as). > > I have managed to display postgis tables before in mapserver, but I am > having no luck in this one and cannot see any differences. Both the > shapefile and postgis table can display in qgis. My map file is listed > below. I have intentionally tried to simplify this down to the basics > so it has no projection details, etc at present. Any help would be > greatly appreciated. Thanks > > MAP > NAME "test" > IMAGETYPE PNG >SIZE 400 500 >STATUS ON >EXTENT 200949 883531 347001 979508 >SHAPEPATH "C:\ms4w\Apache\htdocs\test" >UNITS METERS > END > >WEB > IMAGEPATH "c:\ms4w\tmp\ms_tmp\" > IMAGEURL "/ms_tmp/" >END > > #Region Polygons >LAYER >NAME "North" >CONNECTIONTYPE postgis > CONNECTION "user=postgres password=1 > dbname=test host=localhost port=5432" >DATA "the_geom from North" >TYPE POLYGON >STATUS DEFAULT >CLASS >STYLE >COLOR 1 1 1 >OUTLINECOLOR 1 1 1 >END >END >END > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://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 //* wrote: >> >> >>From: Paragon Corporation >>Subject: Re: [postgis-users] [postgis-devel] PSC Vote to officially >>drop support for PostgreSQL 8.3 in PostGIS 2.0 >>To: "'PostGIS Development Discussion'" >> >>Cc: "'PostGIS Users Discussion'" >>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 a
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 > An: PostGIS Users Discussion > 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] 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] 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
[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] 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
Re: [postgis-users] How to remotely edit a postgis db over a webclient?
Robert, On 26/01/2011, at 7:01 PM, Robert Buckley wrote: > Slight variation of the first question, but still very relevant.If I have > non-gis users who want to edit the data, are there any examples of webclients > that could also do simplified editing?.. Do you mean just edit tables of attributes? You could use pgAdmin to do this. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Creating density map
Aren, You might have more luck on the R-sig-geo list. This looks like a mix of network analysis and point processes - check out pgRouting, or look for routing algorithms check out the sp package and then spatstat, network etc. An approach (by no means the right or even a good one) is to think of it as a graph or MCMC problem, and consider the relationship between the events as probabilities that can be affected by distance. You are of course trying to create a complex spatio-temporal model like everyone, so there is a fair bit of literature out there. It probably pays to look at relevant articles in your field. Once you have this clearer, I think you should be able to work out how to get the data into R - but it could either be as a data frame using spatial SQL to return attributes, or using readOGR if you need spatial objects in R cheers Ben On 10/01/2011, at 2:50 PM, Aren Cambre wrote: > I have three datasets: > Routes > Event type A that occurs along the routes (points) > Event type B that occurs along the routes (points) > Both event types have several attributes, including a date/timestamp, > sub-classes of each event type, and other meaningful attributes. > > I'm trying to use statistical methods to check for certain relationships > between event types A and B. They may influence each other (A may affect B > and B may affect A). I also want to see if there's a relationship between > subtypes. E.g., do events A.X or A.Y have a stronger impact on event type B? > > I'd like to make heat density maps to help interpret the data, but I have two > conceptual problems. > > First problem is how to make the map. The programmatically easy but slow way > is to create a greedy algorithm to traverse every route. During traversal, > create a point at each increment of distance X. An attribute of that point > may be the number of qualifying events no more than distance Y from that > point. > > I may need to limit to events along the route I am traversing. E.g., if > traversing route M looking for event type B, and I come across route N, the > heat map for route M probably should not include events of type B along route > N event if they are within Y distance from my current point. > > Second problem is how to deal with all the permutations. I could muck through > the simple algorithm and make spiffy point maps, and with a little graphical > wizardry, I could make the maps pretty. However, I need to do analysis over > different time periods. E.g., does the relative intensity of week I's event > type As along route M affect the occurrence of event type B on week I+1? How > about event type A.X? A.Y? Do they have different effects over the same time > period? I have between 3 and 9 years of event types A and B... > > All the permutations (not simply combinations) of factors can really explode > the complexity of this project. > > To prevent wheel reinventing, are there already well-tread solutions to this > problem? I've done some Google searches and am not coming up with much, so I > guess I may not be using the correct lingo? > > I know that I need to incorporate R into this at some point; my objective now > is to get the data to a point where I could use R to analyze it. > > 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] How to reproject data with custom projection?
Aren, You need a reference projection, you are correct. For the sake of the exercise, bookmark http://spatialreference.org/ and then try the search box for parts of the string which are maybe unusual : I tried "Standard_Parallel_1",27.5 Lambert_Conformal_Conic in the search box. then I reckon you can probably choose any of the three results I get - there appears to be very little difference between them and your string. If you haven't used this site before, looking at the Well Known Text as HTML Human-Readable OGC WKT Proj4 OGC WKT JSON GML ESRI WKT .PRJ File options will help. Good luck (from the antipodes, so take your chances) cheers Ben On 09/01/2011, at 3:18 PM, Aren Cambre wrote: > I have a SHP with this in its PRJ file: > > PROJCS["Custom",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]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",150.0],PARAMETER["False_Northing",500.0],PARAMETER["Central_Meridian",-100.0],PARAMETER["Standard_Parallel_1",27.5],PARAMETER["Standard_Parallel_2",35.0],PARAMETER["Central_Parallel",18.0],UNIT["Meter",1.0]] > > I need to get this into 3081 somehow since it's a good projection for Texas. > > I have some 3081-reprojected PoistGIS data (originally 4269) for Texas > already. If I load this SHP into QGIS along with other 3081 data, it appears > well below the other data. QGIS indicates this SHP has a custom projection. > > If I am not mistaken, to reproject with PostGIS, I need something besides -1 > in the geometry table. So I guess I need to specify some SRID with -s when I > run shp2pgsql? If so, what projection is this? > > Thanks, and I apologize in advance for my naivety. > > 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] 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 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] basic functionalities of PostGIS
Apostolis, On 04/01/2011, at 4:55 AM, ÎÎ ÎΣΤÎÎÎΣ ÎÎÎÎÎΤÎÎΣ wrote: > Indeed, i understand the power of spatial SQL, but now i am a beginner, and i > need a lot of work. > > As i mentioned, i want to union two layers, typing a spatial SQL query. > I want to take the same result, if i use the tool union of ArcGIS. But, i > don't like to talk with terminal of ArcGIS, so i want to execute one of the > classic spatial action, namely UNION (A or B). > Because, i can't type the right command..could you help me??? It is fairly well documented, but this example unions two polygons (A Red, B Green). <> SELECT ST_AsText( ST_Union( ST_GeomFromText( 'POLYGON (( 1 1,1 3, 3 3 ,3 1,1 1))' -- Polygon A ), ST_GeomFromText( 'POLYGON((2 2, 2 4, 4 4, 4 2, 2 2))' -- Polygon B ) ) ); st_astext POLYGON((1 1,1 3,2 3,2 4,4 4,4 2,3 2,3 1,1 1)) (1 row) ___ 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 wrote: > > From: Aren Cambre > Subject: Re: [postgis-users] Traverse set distance along a multiline? > To: "PostGIS Users Discussion" > 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 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 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 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 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 > >> > calcu
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 : <> cheers (and Happy Christmas etc) Ben___ 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 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
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 wrote: > > From: Ben Madin > Subject: [postgis-users] Shifting linestrings left > To: "PostGIS Users Discussion" > 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
[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] 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
[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
