[TinyOWS-users] Problem inserting features

Carlos Ruiz boolean10001 at yahoo.com
Tue Sep 20 09:53:59 EST 2011


When you use the shp2pgsql utility, it adds to the table some constraints to ensure that all the geometry insertion/update will be of the same SRID, type and dimension.

So, the definition

ALTER TABLE saunders_lines ADD CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(the_geom) = 900913);

ensures that the SRID of the geometry to insert/update will always be 900913.

You can change the SRID of the geometry if you want to using ST_TRANSFORM, but you first must drop the enforce_srid_wkb_geometry constraint and replace the new value in the postgis spatial_columns table for the saunders_lines row.

The 900913 projection treats the earth as a sphere (this is wrong) and handles the coordinates as X/Y instead as Lat/Lon. You can transform your geometry to SRID 4326 which treats the earth as a geoid, then the coordinates are Lat/Lon and there's no problem with its definition.

Why don't you try this ?

IC Carlos Ruiz

From: Brian May <bmay at mapwise.com>
To: Carlos Ruiz <boolean10001 at yahoo.com>; TinyOWS users discuss list <tinyows-users at lists.maptools.org>
Sent: Monday, September 19, 2011 9:33 PM
Subject: Re: [TinyOWS-users] Problem inserting features

More on 900913 issues:

Just for kicks I decided to install QGIS 1.7 - it will make my life
    better, right? I go and open up my project that I had been testing
    WFS-T with, test adding a line and saving it - new problem - ERROR:
    new row for relation "saunders_lines" violates check constraint

I didn't change anything. 

So, after checking out projection defs in different places I find

QGIS 1.7 definition of 900913
+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0
    +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +over +no_defs

QGIS 1.7 definition of 3785
+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0
    +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs

QGIS 1.7 3875 (new Google Mercator Standard)
Not there!

From WFS layer metadata (coming out of postgres)
+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0
    +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +over +no_defs

From postgres spatial_ref_sys table
+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0
    +y_0=0 +units=m +k=1.0 +nadgrids=@null +no_defs

End of a thread on the QGIS list discussing these EPSG codes.

The constraint on my table for srid:
ALTER TABLE saunders_lines
  ADD CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(the_geom) =

So maybe I'm not making things easy on myself having the data in
    900913 (or one of its variants)? 


On 9/19/2011 5:05 PM, Carlos Ruiz wrote: 
Just about the 900913 projection, maybe it is not found in the spatial_ref_sys table, also in Mapserver could be not found in proj4, so I suggest to try with the EPSG:3857 which is the same for spherical mercator.
>Cheers from México
>IC Carlos Ruiz
>From: Brian May <bmay at mapwise.com>
>To: TinyOWS users discuss list <tinyows-users at lists.maptools.org>
>Sent: Monday, September 19, 2011 3:22 PM
>Subject: [TinyOWS-users] Problem inserting features
>Getting TinyOWS to work has been a long and winding road - still no success - and almost ready to give up. Here's the latest on what I have tried and the results.
>I ended up trying it out on linux and got further down the
              road, but different problems. I compiled it from source
              from SVN. I can insert features via QGIS and OpenLayers
              now, but the inserted features do not have any geometry
              (confirmed via checking out the table in postgres). And I
              can see existing features in QGIS that I manually added
              via a direct connect to postgres, but not in OpenLayers! I
              have been running down a lot of rabbit holes trying to get
              this figured out, burning a lot of time and I could really
              use some help. 
>Also, I may have found a few bugs in the process. For
              example, if you have two geometry columns in your table,
              and do not inform tinyows of the additional geometry
              column via the config file, it produces a 500 error when
              testing from the browser. Does tinyows expect the_geom vs.
              wkb_geometry? Tried both and it seems to operate the same,
              except when you have them both there at the same time.
>I don't remember if this is in the docs, but you must have
              a record in the geometry_columns table for tinyows to
              recognize the layer at all - you can have your config all
              set up right, and it just won't show the layer as being
>If you change the geometry column name in your table and
              the geometry column name in the geometry_columns table
              don't match - doing ./tinyows --check produces the
              following error:
>row number 0 is out of range 0..-1
>tinyows: src/struct/buffer.c:254: buffer_add_str:
              Assertion `str' failed.
>Is there any reason why having your data in 900913
              projection would be a problem? Wasn't my first choice, but
              I figured i could eliminate projection problems that way
              since the map is in 900913.
>Also, not sure why, but after some change I made, the url http://myurl/cgi-bin/tinyows?service=WFS&request=DescribeFeatureType&version=1.1.0& is producing a much more terse output than before. Haven't figured out what it is yet, although if I take out my custom layer, and just have the france demo, the verbose output comes back.
>If its easier to help debug this, I can open it up to the
              outside world temporarily. 
>Any help would be greatly appreciated.
>TinyOWS-users mailing list
>TinyOWS-users at lists.maptools.org
TinyOWS-users mailing list TinyOWS-users at lists.maptools.org http://lists.maptools.org/mailman/listinfo/tinyows-users 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.maptools.org/pipermail/tinyows-users/attachments/20110920/65a11434/attachment-0001.htm 

More information about the TinyOWS-users mailing list