Re: [postgis-users] ERROR: geometry contains non-closed rings
On Aug 18, 2011, at 10:04 PM, Paul Ramsey wrote: > I think you're going to have to write a little routine to close your > rings... it looks like the WKB emitter is hard-coded to error out when > asked to serialize broken things (hm I thought this was changed, > the neverending story...) For what its worth, I am using "POSTGIS="1.5.3" GEOS="3.3.0-CAPI-1.7.0" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS" As mentioned in my reply to Ben Madin's suggestion, when I run the following SELECT gid FROM data WHERE st_isclosed(the_geom) IS FALSE; I get no results. > > You can patch it your self by changing this line > > http://trac.osgeo.org/postgis/browser/tags/1.5.3/postgis/lwgeom_inout.c#L307 > > from PARSER_CHECK_ALL to PARSER_CHECK_NONE > > P. > > On Thu, Aug 18, 2011 at 9: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
Re: [postgis-users] ERROR: geometry contains non-closed rings
Ben, On Aug 18, 2011, at 10:04 PM, Ben Madin wrote: > SELECT gid, st_isvalidreason(the_geom), st_summary(the_geom) FROM data WHERE > st_isclosed(the_geom) is FALSE; I am not entirely sure what the above was supposed to accomplish (I am assuming it would have given me the reason for features being invalid), however, I ran the query and I got 0 (zero) rows. Puneet. ___ 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
[postgis-users] closest point
Hi, I'm trying to get the closest point to a given one, complete SQL is as follows: """ select DISTINCT fl.stime, ll.latitude, ll.longitude, (select (case when data_origin = 'SNT' then lic_name else lic_tx_name end) as lic_tx_name from transmitter_mv where tx_freq::decimal = fl.frequency::decimal --and st_dwithin(punto, st_makepoint(ll.longitude, ll.latitude), 50, false) -- order by st_distance(punto, st_makepoint(ll.longitude, ll.latitude)) and st_distance_sphere(transmitter_mv.punto, ll.punto) < 50 order by st_distance_sphere(transmitter_mv.punto, ll.punto) limit 1 ) as estacion, fl.level, fl.frequency / 100 as frecuencia from rowfreqlevel fl join rowlatlong ll ON ll.row_id = fl.row_id WHERE stime >= '2011-05-24 23:39' AND stime <= '2011-05-24 23:41' """ as you see, for every set of long/lat i need to find the closest transmitter. i tried it creating a point in the query and also adding a point column in the table (in both cases i created gist indexes, the first time over the st_makepoint() function then over the geometry column) nevertheless i always get a seq scan. any idea how to improve this query? just in case here is the plan: http://explain.depesz.com/s/UY3 -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL Soporte 24x7, desarrollo, capacitación y servicios ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Importing tiles in PostGIS raster
postgis-users-boun...@postgis.refractions.net wrote on 08/18/2011 08:58:29 AM: > Hi, > > I want to import a raster image in PostGIS. Now my raster image is already > split into tiles and I can't see how I can handle those images with > raster2pgsql.py. Currently the filenames are x_y.jpg where x and y > refer to the > coordinate of the upper left corner. How can I import those images using the > correct coordinates? All examples just mention the SRID and no positions. Raster assumes you're using a geospatially aware format. Hence, given the metadata in the file, it can position the entire image correctly. In this case, it means that each individual x_y.jpg will be correctly positioned. You can also access pixels by real-world-coordinate fairly easily. Preserving the original pixel indices is not its strong point. Each tile starts at (1,1) (or (0,0) can't remember which). ___ 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
David, I enabled debugging (pl see map file) and got the following error log: [Thu Aug 18 11:05:26 2011].929000 msPostGISLayerWhichShapes(): Query error. Error (ERROR: function force_2d(geometry) does not exist Where should I go from here? Thanks for the error logging how-to, V -- LINE 1: select encode(AsBinary(force_collection(force_2d("geom")),'N... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ) executing query: select encode(AsBinary(force_collection(force_2d("geom")),'NDR'),'hex') as geom,"gid" from "wardscleanattributes" where geom && GeomFromText('POLYGON((77.5 12.8982149362477,77.5 13.0617850637523,77.7 13.0617850637523,77.7 12.8982149362477,77.5 12.8982149362477))',4326) [Thu Aug 18 11:05:26 2011].93 msDrawMap(): Image handling error. Failed to draw layer named 'wards'. [Thu Aug 18 11:11:27 2011].929000 msDrawMap(): WMS/WFS set-up and query, 0.000s [Thu Aug 18 11:11:27 2011].98 msPostGISLayerWhichShapes(): Query error. Error (ERROR: function force_2d(geometry) does not exist LINE 1: select encode(AsBinary(force_collection(force_2d("geom")),'N... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ) executing query: select encode(AsBinary(force_collection(force_2d("geom")),'NDR'),'hex') as geom,"gid" from "wardscleanattributes" where geom && GeomFromText('POLYGON((77.5 12.8982149362477,77.5 13.0617850637523,77.7 13.0617850637523,77.7 12.8982149362477,77.5 12.8982149362477))',4326) [Thu Aug 18 11:11:27 2011].981000 msDrawMap(): Image handling error. Failed to draw layer named 'wards'. [Thu Aug 18 11:11:27 2011].981000 msFreeMap(): freeing map at 0165CE90. - MAP IMAGETYPE PNG EXTENT77.5 12.95 77.7 13.01 SIZE550 450 IMAGECOLOR 255 255 255 SHAPEPATH "../data" CONFIG "MS_ERRORFILE" "../ms_error.txt" DEBUG 5 # Start of LAYER DEFINITIONS--- LAYER CONNECTIONTYPE POSTGIS NAME "wards" # Connect to a remote spatial database CONNECTION "host=localhost port=5432 dbname=bangalore user=postgres password=xxx" # Get the lines from the 'geom' column of the 'wards' table DATA 'geom from "wardscleanattributes" using srid=4326 using unique gid' STATUS DEFAULT TYPE POLYGON CLASS NAME "wards" STYLE OUTLINECOLOR 255 0 0 END END END # End of LAYER DEFINITIONS --- END - -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of David Fawcett Sent: Wednesday, August 17, 2011 7:33 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] connecting to postgis form mapserver Vishal, As Ben said, enabling debugging is the best way to figure out what is going on. There is some pretty good documentation on how to do that at: http://mapserver.org/optimization/debugging.html If that doesn't help you, it is probably time to move this over to the MapServer list. (Where Ben and I will also be likely hanging out...) David. On Wed, Aug 17, 2011 at 7:44 PM, Ben Madin wrote: > 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 > SIZE 550 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/a
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] ERROR: geometry contains non-closed rings
I think you're going to have to write a little routine to close your rings... it looks like the WKB emitter is hard-coded to error out when asked to serialize broken things (hm I thought this was changed, the neverending story...) You can patch it your self by changing this line http://trac.osgeo.org/postgis/browser/tags/1.5.3/postgis/lwgeom_inout.c#L307 from PARSER_CHECK_ALL to PARSER_CHECK_NONE P. On Thu, Aug 18, 2011 at 9:19 AM, Mr. Puneet Kishor wrote: > > I loaded a bunch of data (that came from ArcMap) into PostGIS (1.5.3) via > shp2pgsql and am trying to draw it with MapServer 6.0.1. I get > >> msDrawMap(): Image handling error. Failed to draw layer named 'foo'. >> msPostGISLayerWhichShapes(): Query error. Error (ERROR: geometry contains >> non-closed rings ) > > > Many thanks in advance for your suggestions. > > -- > Puneet Kishor > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ERROR: geometry contains non-closed rings
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
Re: [postgis-users] Cursor names in a self-nested function
Appologies, I posted this to the wrong list. David M. Kidd Research Associate Center for Population Biology Silwood Park Campus Imperial College London 0207 594 2470 From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Kidd, David M Sent: 18 August 2011 13:44 To: PostGIS Users Discussion Subject: [postgis-users] Cursor names in a self-nested function Hi, I am trying to write a function that contains a cursor and iteratively calls itself. It is along the lines of, CREATE FUNCTON test(id integer) RETURNS TEXT AS $BODY$ DECLARE mycursor CURSOR FOR SELECT * FROM myfunction(id); newid INTEGER; out = TEXT; BEGIN out := ''; OPEN mycursor; LOOP FETCH my_cursor INTO newid; out := out || test (newid); END LOOP; RETURN out; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE This returns an ERROR stating that "mycursor" is already in use. I understand this occurs because cursor names must be unique across, as well as within, functions. So, my question is whether there is a way I can dynamically declare a cursor name, for example by appending a incremental number or guid to make the name unique? Just trying to concatenate two passed arguments in the DECLARE statement unsurprisingly fails. Any other solutions are of cause welcome. Many thanks, - David David M. Kidd Research Associate Center for Population Biology Silwood Park Campus Imperial College London 0207 594 2470 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Importing tiles in PostGIS raster
To load many raster at the same time use the * wildcard. E.g. raster2pgsql.py -r c:/yourfolder/*.jpg To georeference your tiles properly, you could use the -F raster2pgsql option to create a new column with the original filename and then use string functions to extract the upper left X an upper left Y and set them in the raster. If your X and Y coordinates are encoded on three digits you could do something like: UPDATE yourrasttable SET rast = ST_SetUpperLeft(rast, substring(filename, 1, 3)::float4, substring(filename, 5, 3)::float4) You can also set the pixel size at the same time: UPDATE yourrasttable SET rast = ST_SetUpperLeft(ST_SetScale(rast, 0.34256), substring(filename, 1, 3)::float4, substring(filename, 5, 3)::float4) Hope this help, Pierre > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Knut Krause > Sent: Thursday, August 18, 2011 4:58 AM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] Importing tiles in PostGIS raster > > Hi, > > I want to import a raster image in PostGIS. Now my raster image is already > split > into tiles and I can't see how I can handle those images with raster2pgsql.py. > Currently the filenames are x_y.jpg where x and y refer to the coordinate of > the > upper left corner. How can I import those images using the correct > coordinates? > All examples just mention the SRID and no positions. > > Would be great if someone has experience with that. > > Regards > > > Knut ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Cursor names in a self-nested function
Hi, I am trying to write a function that contains a cursor and iteratively calls itself. It is along the lines of, CREATE FUNCTON test(id integer) RETURNS TEXT AS $BODY$ DECLARE mycursor CURSOR FOR SELECT * FROM myfunction(id); newid INTEGER; out = TEXT; BEGIN out := ''; OPEN mycursor; LOOP FETCH my_cursor INTO newid; out := out || test (newid); END LOOP; RETURN out; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE This returns an ERROR stating that "mycursor" is already in use. I understand this occurs because cursor names must be unique across, as well as within, functions. So, my question is whether there is a way I can dynamically declare a cursor name, for example by appending a incremental number or guid to make the name unique? Just trying to concatenate two passed arguments in the DECLARE statement unsurprisingly fails. Any other solutions are of cause welcome. Many thanks, - David David M. Kidd Research Associate Center for Population Biology Silwood Park Campus Imperial College London 0207 594 2470 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Importing tiles in PostGIS raster
Hi, I want to import a raster image in PostGIS. Now my raster image is already split into tiles and I can't see how I can handle those images with raster2pgsql.py. Currently the filenames are x_y.jpg where x and y refer to the coordinate of the upper left corner. How can I import those images using the correct coordinates? All examples just mention the SRID and no positions. Would be great if someone has experience with that. Regards Knut signature.asc Description: This is a digitally signed message part. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: Unknown geometry type: 0 - Unknown
But is it correct that to be able to create a backup, tables creating these errors must be dropped from the DB before a proper backup can be done? I could not continue my backup process before these tables were dropped. Thank you for your help Sandro. Andreas 2011/8/17 Sandro Santilli > On Wed, Aug 17, 2011 at 04:03:43PM +0200, Andreas Forø Tollefsen wrote: > > Great. Thank you Sandro. > > > > That worked. I managed to install the 6874 SVN and are now able to query > the > > geometry columns as well. > > > > I then tried to backup the database, but got an error on two of my tables > > regarding the function postgis_typmod_out. > > Both of the tables in question have been read from a CSV file into a > table > > in the database. I dropped these tables and now i can backup the > database. > > The complete error: "ERROR: could not find the function > "postgis_typmod_out" > > in file "/usr/lib/postgresql/8.4/lib/postgis-2.0.so" > > Don't worry about those, must be new functions defined in newer postgis > that weren't available in older one. > > > By the way. Can i actually have two databases built from two different > SVN > > revisions at the same time? Is that possible at all? > > Not easily. > You'd have to install libraries in two different directories and reference > the correct one from each postgis.sql file. > > --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] st_geometryn
> Yes. > > But how does it determine which is the first geometry. > > I've been using it on multipoints and in those cases the first point is on > the left with the horizontal plane and the bottom with the vertical. > > Bob > Ok, sorry. I don't in which order they are output, maybe the creation order ? Depending on how they were stored (index use, order clause ?) Nicolas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users