[postgis-users] how to transfer wkt geometry from mysql to postgis?
Hi, I have wkt geometry data in mysql stored in blob format. I need to get this data into a postgis db and am looking for the quickest-easiest way to do it. I have managed to select the data ASTEXT but can then only copy it into QGIS - export to shape - import to postgisbut this is probably not the easiest way. Is there a way to directly import a table from mysql into postgis? thanks for any help, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are views slow ing down my wms?
Thanks for all your replies and helpful comments. I will be able to do some good analysis next week on the recommendations posted, yours, rob Von: Brent Wood pcr...@yahoo.com An: PostGIS Users Discussion postgis-users@postgis.refractions.net CC: robertdbuck...@yahoo.com Gesendet: 6:19 Samstag, 14.Juli 2012 Betreff: Re: [postgis-users] Are views slow ing down my wms? There are a couple of possibilities: Postgis is slowing down for some reason, or Geoserver is. If you can run a query against the view you can check Postgis performance as Greg suggests. With only 10,000 or so rows in 3 tables it is not likely that Postgis will be slowing down much due to the views, or indexes. Tomcat/Geoserver are perhaps more likely culprits in my experience. You haven't said how much they are slowing down, or how much memory, cpu, etc are available or being used. Try running top in a terminal while it is being used to see which processes are loading up the system, although this will be of limited use with tomcat. Brent Wood --- On Sat, 7/14/12, Greg Williamson gwilliamso...@yahoo.com wrote: From: Greg Williamson gwilliamso...@yahoo.com Subject: Re: [postgis-users] Are views slow ing down my wms? To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Saturday, July 14, 2012, 1:43 PM Robert -- It is possible that you are missing an index on something -- in a smaller table the query planner will ignore an index and do sequential reads because it is a faster strategy; missing an index won't effect a small table but as the # of rows grows the sequential scan becomes much more painful. Might also be a limited amount of memory (work_mem setting) dedicated to searches -- the default works ok for small sorts but at the number of rows being considered grows so too does the memory required. Once postgres reaches the limit set (which is per search and one query may have more than one sort) it dumps the whole search to disk, which can be much slower than the all in RAM version. You might try running the SQL in the view inside an EXPLAIN ANALYZE command and see if there are oddities -- wildly divergent row counts, sequential scans on large tables, etc. You might also post the version of postgres (e.g. 9.1.x) as that may be useful information in seeing what is going on. Also, changes from default postgres settings and information about RAM might help shed some light. Greg Williamson From: Robert Buckley robertdbuck...@yahoo.com To: Post GIS Users Discussion postgis-users@postgis.refractions.net Sent: Wednesday, July 11, 2012 10:30 PM Subject: [postgis-users] Are views slow ing down my wms? Hi, I have created a database structure which has 3 main postgis tables (1 for lines,polygones, and points) which each contain between 5-10,000 features. These tables then feed views which split the geometries into understandable groups which are then served through geoserver in Tomcat6, linux-Ubuntu 10.04. I have noticed that the performance of the wms is getting considerably worse. Is this to do with the views or simply the amount of data being served? Should I in hindsight have split the table into separate tables instead of creating views? thanks for any help, 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Are views slow ing down my wms?
Hi, I have created a database structure which has 3 main postgis tables (1 for lines,polygones, and points) which each contain between 5-10,000 features. These tables then feed views which split the geometries into understandable groups which are then served through geoserver in Tomcat6, linux-Ubuntu 10.04. I have noticed that the performance of the wms is getting considerably worse. Is this to do with the views or simply the amount of data being served? Should I in hindsight have split the table into separate tables instead of creating views? thanks for any help, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Changing regional setting for a numeric field in a single database
Hi, on trying to import data from csv I am now having a problem with regional settings. At the moment it looks like this; db=#show client_encoding; UTF8 db=#show lc_numeric; de_DE.utf8 The problem is that I have getting this error; ERROR: invalid input syntax for type numeric: 2.000,000 I have defined this field as... numeric(11,4) and thus the problem is that the decimal and thousand separators are mixed up. Would anyone know how I can set this properly to this one database? Thanks for any help, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] versioning control: anyone tried this?
Hi, I have just read about this.. http://www.kappasys.ch/cms/index.php?id=23L=5 Does anyone have any experience with it? does it work? Problems? etc. Great to hear from anyone who has positive experience with it, yours, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] migrating mysql database with skt geometries into a postgis db?
Hi, I have this taskanyone got any tips on how to acheive thisß yors, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Boundary(geometry) for polyon to line conversion?
Hi, ST_Boundary(geometry) Is this the function I would use to convert a polygon to a polyline? yours, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] change the encoding for existing table
Hi, I am testing the SpatialKit extention for ArcGIS which uploads data into Postgresql. When uploading into the database there is not an option to specify encoding types. Usually I choose latin1 over UTF-8 because latin1 enables the use of german characters (ä,ö,ü andß) although i have never understood why I should this instead of UTF-8 which apparently also contains these characters. Anyway...The tables created by the SpatialKit extention seem to cause a problem with these characters. In order to test if the problem really is to do with the encoding, I would like to change it to latin1. How can I do this in postgresql on a postgis table? cheers, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Editing with arcgis without ArcSDE
Hi, I am trying to find a way to edit postgresql geospatial data with ArcGIS but without ArcSDE. I have found ST-LInks SpatialKit (http://www.st-links.com/Pages/default.aspx) which I have installed on ArcGIS 9.3.1 and on another PC with ArcGIS 10. After successfully installing I can´t load a sinlge layer into ArcMap!!always an error message which cannot be clicked off or ArcGIS Crashes!!! Really rubbish software, and they want people to pay for it!?! Anyway. does anyone else know of any other software tools that enable connections with ArcGIS for editing geometries? Thanks for any tips, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Any other way to use alias headers for fields?
Hi, I was just wondering if there is any other way in postgresql in which I could assign alias names to columns other than creating views and assigning the field names with the AS operator? I have shapefiles with column names which have cryptic titles. When I upload them into postgis and serve them in my viewer application, the original cryptic column names will be displayed. It would be nice to change them for the use in the viewer application but have the original names retained also to ensure that updates to the data can be done easier. yours, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] how to create a hole in a polygon table?
Hi, I am trying to create an inner buffer of a polygon geometry table. Unfortunately this is the only way I can think of which will enable me to make a certain symbol for a polyon layer. The SLD Filter difference might do it, but it needs to be nested and i´m not sure if this is possible. anywayMy polygons need to be symbolized with a thin outline AND an inside Buffer of 3 mm(150m at a scale of 1:50 000)in essence just an inside ring. This means I need to first buffer my polygon geometry and then substract the buffer from the original polygon. = ST_Difference (i think!?!) my attempt created some geometries but not what I was expecting... create view rrop08.rrop_01_13_innerbuffer AS SELECT ST_Difference(a.st_buffer, b.the_geom) FROM rrop08.rrop_01_13_buffer a, rrop08.rrop_01_f b; rrop08.rrop_01_f = the original polyon rrop08.rrop_01_13_buffer = the inner buffer ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to create a hole in a polygon table?
Thanks for the advice. Unfortunately I don´t understand what you have just told mehalf-brush? half-circle? are these sld specifications?...I am delivering wms data using sld´s to style the data. could you please clarify? cheers, Rob Von: Paul Ramsey pram...@opengeo.org An: Robert Buckley robertdbuck...@yahoo.com; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 19:20 Dienstag, 28.Februar 2012 Betreff: Re: [postgis-users] how to create a hole in a polygon table? Rather than geometry processing, work on your rendering engine. You can usually achieve an interior band (the classic National Geographic political boundary, yes?) with a half-brush (rather than a full circle, use a half-circle), and just ensuring your polygons are consistently oriented. P. On Tue, Feb 28, 2012 at 10:04 AM, Robert Buckley robertdbuck...@yahoo.com wrote: Hi, I am trying to create an inner buffer of a polygon geometry table. Unfortunately this is the only way I can think of which will enable me to make a certain symbol for a polyon layer. The SLD Filter difference might do it, but it needs to be nested and i´m not sure if this is possible. anywayMy polygons need to be symbolized with a thin outline AND an inside Buffer of 3 mm(150m at a scale of 1:50 000)in essence just an inside ring. This means I need to first buffer my polygon geometry and then substract the buffer from the original polygon. = ST_Difference (i think!?!) my attempt created some geometries but not what I was expecting... create view rrop08.rrop_01_13_innerbuffer AS SELECT ST_Difference(a.st_buffer, b.the_geom) FROM rrop08.rrop_01_13_buffer a, rrop08.rrop_01_f b; rrop08.rrop_01_f = the original polyon rrop08.rrop_01_13_buffer = the inner buffer ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to create a hole in a polygon table?
Thanks for that...shame it didn´t work. I have already looked at negative buffers, but they create a polygon within a polygon and not a hole within a negative-buffered polyon. i.e my polygon needs to be transparent in the center past the negative buffer so underlying features can be seen. Andrea Aime thought it could be done with the Difference SLD filter, but I can´t seem to get it to work due to my lack of understanding of how the filter works. http://docs.geoserver.org/latest/en/user/styling/sld-reference/filters.html yours, Rob Von: Paul Ramsey pram...@opengeo.org An: Robert Buckley robertdbuck...@yahoo.com; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 22:33 Dienstag, 28.Februar 2012 Betreff: Re: [postgis-users] how to create a hole in a polygon table? I was thinking of something like this, but it doesn't work. Unfortunately Geoserver doesn't have the ability to create offset lines, so there's no nice way to do this without doing a little negative buffer on the shape (which incidentally, you could do using the geometry transformation facility on the fly http://docs.geoserver.org/stable/en/user/styling/sld-extensions/geometry-transformations.html P ?xml version=1.0 encoding=ISO-8859-1? StyledLayerDescriptor version=1.0.0 xmlns=http://www.opengis.net/sld; xmlns:ogc=http://www.opengis.net/ogc; xmlns:xlink=http://www.w3.org/1999/xlink; xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance; xmlns:gml=http://www.opengis.net/gml; xsi:schemaLocation=http://www.opengis.net/sld http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd; NamedLayer NameUSA states/Name UserStyle Namepopulation/Name TitleUnited States/Title AbstractA Simple Brush Style/Abstract FeatureTypeStyle Rule LineSymbolizer Stroke GraphicStroke Graphic ExternalGraphic OnlineResource xlink:type=simple xlink:href=file:///Users/pramsey/green_bottom.gif / Formatimage/gif/Format /ExternalGraphic /Graphic /GraphicStroke CssParameter name=stroke-linecapround/CssParameter CssParameter name=stroke-dasharray1 0 1 0/CssParameter /Stroke /LineSymbolizer /Rule /FeatureTypeStyle /UserStyle /NamedLayer /StyledLayerDescriptor On Tue, Feb 28, 2012 at 12:00 PM, Robert Buckley robertdbuck...@yahoo.com wrote: Thanks for the advice. Unfortunately I don´t understand what you have just told mehalf-brush? half-circle? are these sld specifications?...I am delivering wms data using sld´s to style the data. could you please clarify? cheers, Rob Von: Paul Ramsey pram...@opengeo.org An: Robert Buckley robertdbuck...@yahoo.com; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 19:20 Dienstag, 28.Februar 2012 Betreff: Re: [postgis-users] how to create a hole in a polygon table? Rather than geometry processing, work on your rendering engine. You can usually achieve an interior band (the classic National Geographic political boundary, yes?) with a half-brush (rather than a full circle, use a half-circle), and just ensuring your polygons are consistently oriented. P. On Tue, Feb 28, 2012 at 10:04 AM, Robert Buckley robertdbuck...@yahoo.com wrote: Hi, I am trying to create an inner buffer of a polygon geometry table. Unfortunately this is the only way I can think of which will enable me to make a certain symbol for a polyon layer. The SLD Filter difference might do it, but it needs to be nested and i´m not sure if this is possible. anywayMy polygons need to be symbolized with a thin outline AND an inside Buffer of 3 mm(150m at a scale of 1:50 000)in essence just an inside ring. This means I need to first buffer my polygon geometry and then substract the buffer from the original polygon. = ST_Difference (i think!?!) my attempt created some geometries but not what I was expecting... create view rrop08.rrop_01_13_innerbuffer AS SELECT ST_Difference(a.st_buffer, b.the_geom) FROM rrop08.rrop_01_13_buffer a, rrop08.rrop_01_f b; rrop08.rrop_01_f = the original polyon rrop08.rrop_01_13_buffer = the inner buffer ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ..
How are you doing? http://waterultracare.zymichost.com/httpworkathome19385.php?ysipidname=100 Wed, 1 Feb 2012 21:07:08 __ Hooper, while a rough-and-ready old chap, with a big fortune made in cattle dealing, is really an uncut diamond; a fine old fellow at heart, as you will see.Two busy days followed during which Bill and Gus went to the city with Professor Gray to purchase materials in full for the power plant. (c) Ananda whigham3 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Can´t exit comand after command
Hi, Probably a newbie question, but in putty, when I do certain commands e.g \dt *.* and the results go over more than one page, I can´t seem to exit or end the command. The page ends with the word (END) highlighted and I can´t do anything to start another command. I´ve tried Ctrl d but nothing happens Is this a putty problem or a postgresql prob? thanks for anby help, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Trignometry and triangulation to find location from photo
Hi , I have a task which I though would be a good learning excercise for me, however it turns out that I need to go back to elementary school and pay more attention! I though I would ask the forum incase anyone has done anything similar. It is only marginally related to postgis but if anyone knows of a better forum please let me know. The Problem: I have a postgis database populated with points which represent locations of wind turbines. I need to regularly update the database when new turbines are built or old ones are replaced or removed. Usually I recieve coordinates or a shapefile, but this time I have only recieved a photograph taken at eye-level which show 5 existing wind turbines( for which I have points) and the foundation of the new windturbine presently being built. Using the existing wind turbines as reference points, and knowing their heights is the only information I have to determine the location of the new wind turbine. Using trignometry and triangulation techniques is it possible to determine the location of the new wind turbine? I´m sure there is software to do this, but I thought asking here might bring me closer to an answer quicker. thanks for any help, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] multiple queries in one phpscript?
How does this affect performance? are the queries executed when the javascript scripts are loaded into the browser or only when the chart is displayed in the browser? Von: Nicolas Ribot nicolas.ri...@gmail.com An: Robert Buckley robertdbuck...@yahoo.com; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 11:21 Donnerstag, 6.Oktober 2011 Betreff: Re: [postgis-users] multiple queries in one phpscript? Hi, I am pretty new to postgresql and php. I have a webmapping application which uses postgresql/postgis as its backend. In the application there is a charts website-page which picks out data from the postgis tables and displays them using the Ext.js library. Up until now I have been using one php script which includes one postgresql sql-query which is used to populate the chart. If I have multiple charts which use mulitple queries, am I able to put them in one php script or do I need a seperate script for each query? Please excuse me if this should not be posted here...but I consider this more of a postgis/webmapping question as a sencha/extjs question. thanks, Rob Hi Robert, Yes, you can put any number of queries in a single PHP file. Nicolas___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] schemas and postgis data
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... 1if 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? 2are 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] copying tables from one schema to another
Hi, As a follow-up to my earlier post (Re: [postgis-users] schemas and postgis data) I have another question. I had originally put all my data into the public schema. I want to move my geodata to the schema data, so I just used to following command in the pgadmin3 sql editor create table data.new_table as (select * from public.old_table); although this works, the schema is not copied..ie the primary key and other contraints are missing. So i have done this # first create new table and copy schema create table data.table_2(like table_1 including defaults including constraints including indexes); #then copy data into table_2 insert into data.table2(select * from public.table_1); Is there a better/quicker/safer way of doing this? possibly with pgadmin3?___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] where did pg_toast,pg_toast_temp1 come from?
Hi, when I left work today these pg_toast tables were not in my database. when I looked later ther were. Can anyone tell me where they came from and why they are automatically created in every database? thanks, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?
Thanks, my version is 8.4. The table contains strings but are not too long...under 100char. Thanks, Rob Von: Greg Williamson gwilliamso...@yahoo.com An: Robert Buckley robertdbuck...@yahoo.com; postgis-users@postgis.refractions.net postgis-users@postgis.refractions.net; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 21:19 Donnerstag, 22.September 2011 Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from? Rob -- Hi, when I left work today these pg_toast tables were not in my database. when I looked later ther were. Can anyone tell me where they came from and why they are automatically created in every database? You don't state what version of postgres this happens on, but in general TOAST tables are created by the system to hold long compressed values (typically text aka varlena tables). I think you can turn this facility off, but in general postgres will try to take very long strings, for example, and compress them, putting them into a toast table to that the row size of the original table doesn't grow excessively. See, for example, http://www.postgresql.org/docs/9.0/interactive/storage-toast.html So I suspect what happened is that someone entered some long text values and postgres created the toast tables to handle these long strings. HTH, Greg Williamson___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?
Ok thanks, Is it dangerous to delete toast tables? Von: Greg Williamson gwilliamso...@yahoo.com An: Robert Buckley robertdbuck...@yahoo.com; postgis-users@postgis.refractions.net postgis-users@postgis.refractions.net; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 22:43 Donnerstag, 22.September 2011 Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from? Rob -- ... Thanks, my version is 8.4. The table contains strings but are not too long...under 100char. Thanks, According to http://trac.osgeo.org/postgis/wiki/DevWikiPostGISCoding: All PostGIS objects are varlena, they don't have a fixed size. ... So maybe you got some large geometries that triggered TOAST processing ? Greg W. Von: Greg Williamson gwilliamso...@yahoo.com An: Robert Buckley robertdbuck...@yahoo.com; postgis-users@postgis.refractions.net postgis-users@postgis.refractions.net; PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: 21:19 Donnerstag, 22.September 2011 Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from? Rob -- Hi, when I left work today these pg_toast tables were not in my database. when I looked later ther were. Can anyone tell me where they came from and why they are automatically created in every database? You don't state what version of postgres this happens on, but in general TOAST tables are created by the system to hold long compressed values (typically text aka varlena tables). I think you can turn this facility off, but in general postgres will try to take very long strings, for example, and compress them, putting them into a toast table to that the row size of the original table doesn't grow excessively. See, for example, http://www.postgresql.org/docs/9.0/interactive/storage-toast.html So I suspect what happened is that someone entered some long text values and postgres created the toast tables to handle these long strings. HTH, Greg Williamson ___ postgis-users mailing list postgis-users@postgis.refractions.net http://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] simple puffer script
Hi, I have to use postgis to create a puffer layer. I could do this within postgis, but the problem is that I have to display the result in my webapp! I have seen many examples on how to puffer, but none where a user inputs a value from a textbox e.g 50 and presses a button to calculate a puffer over the data with a 50m puffer and then displays the resulting vector layer in the openlayers map. Is this very complex? How should I start coding this? PHP to script the postgis query? thanks for any scripts, tips or advice. yours, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] using pgShapeLoader to upload csv file
Hi, What is the easiest way to load a table into posgresql? Usually I would just upload a shapefile into the database, but for a specific project I am required to build a relational database system with 1 shapefile and lots of other tables linked with Foreign Keys. I have never done this before for a Postgis project and was wondering on the best way to do this. The tables and shapes have been already created in an ESRI personal geodatabase. thanks for any help, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] getting started with postgis functions and openlayers
Hi, I am pretty well versed with getting data into postgis and displaying them with Geoserver /Openlayers/Geoext etc I am now logically looking into the possibilities of running postgis functions ( that´s what it´s there for!) and displaying the results in my apps. As a newbie in this area, I can´t see how this can happen dynamically. I have carried out spatial functions on data in the posgresql commandline...but what do I do now? Can I make a view of the results? Export the results into it´s own table? Can this be done client-side...ie the user were able to select a point and run a buffer command, how would this then be saved in postgis and then displayed in a map online? Is this even how it all works? thanks for any feedback, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] simple question about views and tables
Hi, I have a table called windturbines. I have created a view called v_sum_yield. I would now like to either create another view which consists of both tables joined with the column bez, or join the view to the original table. The table comprises of single wind turbines and their yield per year. I need to sum the yields according to the region. This table then needs to be served as wfs/wms through geoserver. my first step was... CREATE VIEW v_sum_yield AS select region, sum(yield) AS result from windturbines group by region and then to create another view comprising of an left outer join to the original table... CREATE VIEW v_join AS SELECT * FROM windturbines LEFT OUTER JOIN v_sum_yield ON (windturbines.region = v_sum_yield.region); the result is.. ERROR: column region specified more than onceSQL Status:42701 however if I just run the selection with the CREATE VIEW v_join AS...it works fine. can anyone tell me why? thanks, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] add a rank to population statistics- is this a case for a sequence?
Hi, I have a table containing population statistics. I would like to have an column which automatically updates the rank for each dataset. Would I somehow use a sequence to calculate this, or is there another way to do this? thanks, Rob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] datatype question in postgis
Hi, I have a project on my desktop gis which I would like to transfer to the web and offer as a map service. Currently the project consists of around 90 layers (all shps) -points/lines and polygons. If I serve them through postgresql into geoserver should I keep the data structure as it is, or should I combine all polygons to one shp -all polylines into one shp and points to one shp and then import into three different postgis tables? what are the implications for serving my data? Is it quicker to serve fewer larger tables or many smaller tables? Is there a geometry type which takes all geometries(ie points,lines and polygons)? so that I could serve all my data from one table?...obviously the symbology of 90 classes would make the sld file slightly worrying, but would this be better that having 90 separate sld´s? thanks for any tips, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] images in postgresql
Thanks for all the replies. My Wind turbine table will only have around 300 - maximum 500 points. The photos shouldn´t change until either a turbine vanishes or gets repowered (ie. upgraded). I am pretty new to postgis so when you all start talking about TOAST I start thinking about food rather than data formats. TOAST, Large blob, OID...how do I decide? As the images will be (hopefully) displayed over the web and they shouldn´t be more than 50 kb each. They are simply there to display a nice picture when someonw clicks on the map. The table will however be updated with new turbines and we will certainly find errors so that some will have to be deleted or moved. Would it be easier to keep them all in one table rather than in separate tables? yours, Rob Von: Paragon Corporation l...@pcorp.us An: PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: Dienstag, den 8. März 2011, 6:00:00 Uhr Betreff: Re: [postgis-users] images in postgresql Ben, My understanding is the same (as long as you don't select the column that is) otherwise has to be detoasted. As I recall, I think a small bit will be stored and then the rest that doesn't fit into (I can't recall maximum space), gets chunked into toast records. Its true for most of the databases I've worked with - e.g. large text or blobs just the pointer is stored in the main table, except PostgreSQL makes this decision conditionally on size and other databases make it beforehand based on data type. However -- UPDATES will be painful I think because even though the data is toasted, PostgreSQL will still create an MVCC copy of the whole record when doing updates and slushing around big pictures and geometries can be painful. So if your other wind turbine info gets changed more often than the photos, I would keep them separate. Leo http://www.postgis.us From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Monday, March 07, 2011 8:31 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] images in postgresql Robert, On 06/03/2011, at 4:28 PM, Robert Buckley wrote: Thewindturbine table exists in EPSG:4326. I made a seperate table for the imagesbecause I didn´t wan´t to blow the size of the wind turbine table out ofproportion 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 Iam making a simple application to show wind turbines as wms and I wanted to show the turbine in a popup. I´m not sure how to get the popup to display though. Any examples? Thanks, Rob Von: Paragon Corporation l...@pcorp.us An: PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: Samstag, den 5. März 2011, 18:21:49Uhr Betreff: Re: [postgis-users] images inpostgresql Robert, Is there areason why you have the points in a separate table or do you have points inboth tables and you want to relate by a spatial join? If its a1 to 1 relationship, we would just put them in the sametable. As far asforeign keys go, you should have some identifier the same in the twotables. Do you? So it would beof the form SELECTwt.wt_id, wt.geom, p.picture FROMwindturbines As wt INNER JOIN pictures As p ON wt.wt_id =p.wt_id or if they arespatially related by space SELECT wt.wt_id, wt.geom,p.picture FROM windturbines As wt INNER JOIN pictures Asp ON ST_DWithin(wt.geom, pt.geom, 10) The 10 dependson the spatial reference system or if you are using geography type then itmeans 10 meters. So I'm treating the wind turbine location and picturelocation as the same if they are within 10 meters apart. BTW: you mightwant to read the first chapter of our upcoming book. It's a freedownload and answers this type of question with concreteexamples. 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 n Behalf Of RobertBuckley Sent: Saturday, March 05, 2011 5:39AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] images inpostgresql Hi, Iam just experimenting at the moment with a project and could do with some advice. I have created
Re: [postgis-users] images in postgresql
thanks for the tip, your book looks great...ill have to get it. 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. I am making a simple application to show wind turbines as wms and I wanted to show the turbine in a popup. I´m not sure how to get the popup to display though. Any examples? Thanks, Rob Von: Paragon Corporation l...@pcorp.us An: PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: Samstag, den 5. März 2011, 18:21:49 Uhr Betreff: Re: [postgis-users] images in postgresql Robert, Is there a reason why you have the points in a separate table or do you have points in both tables and you want to relate by a spatial join? If its a 1 to 1 relationship, we would just put them in the same table. As far as foreign keys go, you should have some identifier the same in the two tables. Do you? So it would be of the form SELECT wt.wt_id, wt.geom, p.picture FROM windturbines As wt INNER JOIN pictures As p ON wt.wt_id = p.wt_id or if they are spatially related by space SELECT wt.wt_id, wt.geom, p.picture FROM windturbines As wt INNER JOIN pictures As p ON ST_DWithin(wt.geom, pt.geom, 10) The 10 depends on the spatial reference system or if you are using geography type then it means 10 meters. So I'm treating the wind turbine location and picture location as the same if they are within 10 meters apart. BTW: you might want to read the first chapter of our upcoming book. It's a free download and answers this type of question with concrete examples. http://www.postgis.us/chapter_01 Leo http://www.postgis.us From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Robert Buckley Sent: Saturday, March 05, 2011 5:39 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] images in postgresql Hi, I am just experimenting at the moment with a project and could do with some advice. I have created a database which contains photos of Windturbines. I also have a postgis database with the locations (points) of the wind turbines and would like join the photos to the points via a link table or foreign key. As you can tell, I haven´t too much experience with postgresql and relational database design. But i can imagine that the task should not be too difficult. I am just a bit unsure how to go about it. The photos are on the linux server and the creation of the table and the insert of the image was successfull. But how do i get the join and how would I display this photo in a geoext project? thanks for any tips, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] 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] insert to database - error
Hi, Ubuntu 10.04 geoserver-2.0.2 postgresql 8.4 postgis I am experiencing the following: I can edit a postgis database geometry in Quantum GIS but I can´t insert into a postgis table inserted with shp3pgsql. ( I used the opengeo-suite windows version to upload the shp) my error is here message is here .. http://permalink.gmane.org/gmane.comp.gis.geoserver.user/26390 After days of scratching around the forums and reading this... http://lists.osgeo.org/pipermail/openlayers-users/2010-March/016801.html ..I decided to do the same a make a simple table in postgis - 1 x id column (type - integer, primary key, NotNull) and 1 x the_geom column ( type - geometry) I loaded it into geoserver and was able to start editing immediately Is it then something to do with the constraints inserted by shp2pgsql?..( I removed all of them one by one and tested the insert, but it still didn´t work) Possibly do to with the GID column? I ccan´t remove this column without breaking the geometry structure. Idon´t understand what the problem is with the table imported from a shapefile using shp2pgsql yours, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to set up a db for editing in postgis?
Hi, I have been trying to edit with a postgis databank for ages now and can´t seem to get it working. I have geoserver setup in tomcat6 on ubuntu 10.04. I have postgres8.4 running with postgis. I can connect to postgis through pgadmin I have set up an editing web interface with geoext/openlayers I have tried editing a geoserver layer from a shapefile and it works ok!!! I had to play with the permissions and found that I could only edit when the file permissions were 0777 To edit in postgis I first created a db in postgis called zgb the owner is my postgresql usergeoadmin1 The tables in postgis give all privilages to public In geoserver the files are owned by tomcat6 except www which is owned by geoadmin1 - this is so that I can´t edit the .js and.html in winscp I am really confused as to how to set permissions to be able to edit a postgisdb. How should the privalages be set to be able to edit geometries? If it turns ot that this is not really an issue, what else could be restricting the editing ability of my app? What should I generally watch out for to be able to edit postgis? thanks for any help, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Postgresql 8.4 for Ubuntu 8.04? Compatible????
Hi, I am having errors trying to commit modified features in geoserver. I have a test server where is works, and a work server where it doesn´t. the test server is Ubuntu 10.04 with postgres/postgis 8.4 the work server is Ubuntu 8.04 with postgres/postgis 8.3 ( I can´t upgrade due to virtual server incompatibilties) I wanted to try and isntall postgres 8.4 on Ubuntu 8.3 but in the hardy Universe repositories nothing is found. Does this mean that the versions are not compatible? thanks, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Postgresql 8.4 for Ubuntu 8.04? Compatible????
using this repository i can find postgresql 8.4 but there is not postgis extention. does anyone know if postgis 8.3 can be installed into postgresql 8.4?...or alternatively if I can get postgis8.4 from another secret repository?.am I letting myself in for trouble by doing this? cheers, Robert Von: Nabil Servais nabil.serv...@gmail.com An: PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: Donnerstag, den 27. Januar 2011, 12:23:05 Uhr Betreff: Re: [postgis-users] Postgresql 8.4 for Ubuntu 8.04? Compatible Hello On Thu, Jan 27, 2011 at 12:17, Robert Buckley robertdbuck...@yahoo.com wrote: Hi, I am having errors trying to commit modified features in geoserver. I have a test server where is works, and a work server where it doesn´t. the test server is Ubuntu 10.04 with postgres/postgis 8.4 the work server is Ubuntu 8.04 with postgres/postgis 8.3 ( I can´t upgrade due to virtual server incompatibilties) I wanted to try and isntall postgres 8.4 on Ubuntu 8.3 but in the hardy Universe repositories nothing is found. Does this mean that the versions are not compatible? No that mean, the package for postgresql 8.4 is not available. you have 2 solutions, compile postgresql and postgis or find an repository wich purpose postgresql 8.4 with postgis. I think threre's the backport repo for this. thanks, 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Postgresql 8.4 for Ubuntu 8.04? Compatible????
This backport doesn´t list postgis-8.3 apt-get install postgresql-8.4 -postgis postgresql-contrib-8.4 I was wanted to do this... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Postgresql 8.4 for Ubuntu 8.04? Compatible????
Thanks...but there is an error with the source... Paketlisten werden gelesen... Fertig W: GPG error: http://ppa.launchpad.net hardy Release: Die folgenden Signaturen konnten nicht überprüft werden, weil ihr öffentlicher Schlüssel nicht verfügbar ist: NO_PUBKEY 089EBE08314DF160 W: Probieren Sie âapt-get updateâ, um diese Probleme zu korrigieren. couldn´t check the source?? oh well! never mind Von: Nabil Servais nabil.serv...@gmail.com An: PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: Donnerstag, den 27. Januar 2011, 13:45:04 Uhr Betreff: Re: [postgis-users] Postgresql 8.4 for Ubuntu 8.04? Compatible I give you the adress of the ppa, you have to add this in your source.list. deb http://ppa.launchpad.net/ubuntugis/ubuntugis-unstable/ubuntu hardy main deb-src http://ppa.launchpad.net/ubuntugis/ubuntugis-unstable/ubuntu hardy main On Thu, Jan 27, 2011 at 13:23, Robert Buckley robertdbuck...@yahoo.com wrote: Hi. Sorry about the dumb questions... I tried to enter this into my repository but keep getting errors when I apt-get update. https://launchpad.net/~ubuntugis/+archive/ubuntugis-unstable How can I get the repository to stick? Von: Robert Buckley robertdbuck...@yahoo.com An: PostGIS Users Discussion postgis-users@postgis.refractions.net Gesendet: Donnerstag, den 27. Januar 2011, 13:14:08 Uhr Betreff: Re: [postgis-users] Postgresql 8.4 for Ubuntu 8.04? Compatible This backport doesn´t list postgis-8.3 apt-get install postgresql-8.4 -postgis postgresql-contrib-8.4 I was wanted to do this... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://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] How to remotely edit a postgis db over a webclient?
Hi, 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?.. Yours, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to remotely edit a postgis db over a webclient?
Hi, I am trying to offer partners from my organization the ability to edit my data. 1 possibilty is that I could just instruct them to download the desktop applications so that this would be possible, but then they would need training etc to be able to use it. Some do not even have the permissions to install in there organization. I was actually looking for an opensource web client that could offer editing of postgis through a browser. I have been developing with Openlayer/Geoext but find that for editing purposes it is very complicated and time consuming. I was wondering if there were any out of the box solutions available. GeoMajas looks interesting. I have never worked with maven though. I was hoping for something lighter. yours, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] geoext tutorial - wfs-t editor: save strategy not working.
Hi, after considerable testing, I have found that I am able to move and edit points, but I cannot create new points using the wfs-t editor tutorial. I can however user QGIS to create, edit and move all features. what does this tell me?... I´ve no idea...but it´s not working over a browser! Anyone got any ideas? yours, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to remotely edit a postgis db?
Hi, i have installed postgis on postgresql and everything seems to working fine. At the moment I uploading tables through shp2psql and this is working too. I would now like to edit the data from remote pcs. I have experience using various open source gis software e.g Jump, QGIS, svSIG, Udig etc but I cannot edit and commit changed through these gis systems. My system is Ubuntu 8.04. postgresql is version 8.3 / postgresql-8.3-postgis I installed postgis as sudo...therefore the installation folder is owned by root cd .. /usr/share/postgresql drwxr-xr-x 6 root root 4096 2011-01-25 15:22 8.3 I have created a user called geoserver for gis work sudo -u postgres -i createuser geoserver createdb -E UTF8 -O geoserver gis and then created the language... createlang plpgsql gis I was following these instructions by the way. http://weait.com/content/build-your-own-openstreetmap-server-v1 So...as far as I can see, I have a working installation of postgresql with postgis isntalled. I can log in to postgresql with pgadmin3 and see my gis db which is spatially enabled with the postgis functions. Fantastic! I uploaded a shp and even loaded it into geoserver which displayed it perfectly. so...Now I want to edit the data... Question: 1. does user geoserver have editing permission? How can I find out? 2. is postgres a user account on ubuntu or just the login name for the db?...When I look in my /home folder on ubuntu there is no folder there called postgres so I guess no!?!? 3. Why can´t I just log in through any of the software mentioned above and edit? I would be grateful for any pointers... thanks, Robert Buckley ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to remotely edit a postgis db?
Hi, with Quantum GIS v1.6.0 I just managed to connect and edit!!! I don´t believe i because I was trying all last night and it wasn´t working? ok...so it works! great! As a newbie then, I have a questions? How do I define different users to different tables in a database?...I was thinking of creating various databases for different users. Could I then define different user accounts/roles to the databases to ensure that only correct person is able to edit his/her database?...for example I have a region with 10 counties. If I create 10 databases for each county, I could then assign 10 users (county_1, county_2 ...county_10) to database_1, Database_2...Database_10 etc so that the user County_2 can only edit the database County_2. does that make sense? Is it feasable/doable? yours, Robert ___ 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
Hi, I find it easyest with the built in GUI from the opengeosuite. You can download the community edition and use the whole Shape2postgis import tool as part of the deal here is the link to the download. http://opengeo.org/community/suite/download/ Simply install the desktop version and start the dashboard. In PGAdmin3 for Opengeo there is the llink to the postgis importer tool. Hope this helps, Robert Buckley ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Can anyone interpret this error? ERROR: current transactio
Hi, Ubuntu 8.04 Hardy yours, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Can anyone interpret this error? ERROR: current transaction is aborted, commands ignored until end of transaction block
Hi, What is this error message? Encountered whilst loading data into ubuntu.linux . pgsql: /usr/local/opengeosuite-2.3.0/pgsql/scripts/../../suite/pgdata/medford_taxlots.sql:1305: ERROR: current transaction is aborted, commands ignored until end of transaction block this occurs during the very first startup of opengeo-suite yours, RB ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Can anyone interpret this error? ERROR: current transaction
hi, I outputted the error into a file and found the beginning of the problem.. psql:/home/geoserver/opengeosuite-2.3.0/pgsql/share/contrib/postgis-1.5/postgis.sql:59: ERROR: could not load library /home/geoserver/opengeosuite-2.3.0/pgsql/lib/postgis-1.5.so: /usr/lib/libstdc++.so.6: version `GLIBCXX_3.4.11' not found (required by /home/geoserver/opengeosuite-2.3.0/pgsql/scripts/../../pgsql/lib/libgeos-3.2.3.so) Any logical reason why this couldn´t load? yours, Robert Buckley ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
