PostGIS.cs, bad SRID

Topics: Algorithms, Data Access, SharpMap v0.9 / v1.x, WinForms Controls
Nov 6, 2013 at 12:44 PM
Hello,

i have a PostGIS database with imported data in a special table. If you question the SRID of the imported data by the PostGIS-function getsrid -1 is returned. Data not imported in the table has SRID 25832.

Then i tried to build a layer out of that table. No Features were drawn at all. I traced GetGeometriesInView(Envelope bbox) where an error was thrown in line

features.Add(geom);

Because of that, the line "return Features;" was never reached, leading to no features drawn. The error was that the SRID didn't match.

Would it be an improvement, to question the SRID of the geom? If it doesn't match the SRID of the layer, skip it?
Coordinator
Nov 6, 2013 at 1:15 PM
Are you saying
select "srid" from geometry_columns where "f_table_name" = 'some imported table';
returns -1 and the geometries all have an SRID value of 25832?

If this is the case you should call
select updategeometrysrid('public', 'some imported table', 'geom', 25832);
Hth FObermaier
Coordinator
Nov 6, 2013 at 1:45 PM
Or maybe you even need to populate your geometry column (select populate_geometry_columns();) to get it registered.
how did you import the data?
Nov 6, 2013 at 3:05 PM
Hi Felix, i didn't import the data. i might ask...

No, the entry in geometry_columns is correct (25832). That results in layer.SRID = 25832.

The statement
SELECT getsrid(the_geom) FROM Table;
returns -1 in case of the imported data. Looks for me as if -1 stands for 'not specified'. I used the statement
UPDATE Table SET the_geom=setsrid(the_geom,25832) WHERE getsrid(the_geom)=-1;
to correct that. Works in my development environment. But if my Project is out there in the world and another import is done...

Proposal:
        public override Collection<IGeometry> GetGeometriesInView(Envelope bbox)
        {
            var features = new Collection<IGeometry>();
            using (var conn = new NpgsqlConnection(ConnectionString))
            {
                conn.Open();
                var strSQL = "SELECT \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smGeom_\", getsrid(" + GeometryColumn + ") AS \"SRID\" ";
                strSQL += "FROM " + QualifiedTable + " WHERE ";

                if (!String.IsNullOrEmpty(_definitionQuery))
                    strSQL += _definitionQuery + " AND ";

                if (_supportSTIntersects)
                {
                    strSQL += "st_intersects(\"" + GeometryColumn + "\"," + GetBoxClause(bbox) + ")";
                }
                else
                {
                    strSQL += "\"" + GeometryColumn + "\" && " + GetBoxClause(bbox);
                }

                if (Logger.IsDebugEnabled)
                    Logger.Debug(string.Format("{0}\n{1}", "GetGeometriesInView: executing sql:", strSQL));

                using (var command = new NpgsqlCommand(strSQL, conn))
                {
                    using (var dr = command.ExecuteReader())
                    {
                        var reader = new PostGisReader(Factory);
                        while (dr.Read())
                        {
                            if (dr.IsDBNull(0)) 
                                continue;
                            
                            var geom = reader.Read((byte[])dr.GetValue(0));
                            if (geom != null)
                            {
                               if (SRID==(int)dr[1])
                                    features.Add(geom);
                            }
                        }
                    }
                }
            }
            return features;
        }
Coordinator
Nov 6, 2013 at 3:20 PM
Edited Nov 6, 2013 at 3:25 PM
But how did the geometry with an srid of -1 get into a geometry columnt that was defined with an srid of 25832.
That must have violated some constraint. On a postgis 2.0 server I get the following:
insert into "ttt" values(1000, st_makeline(st_makepoint(0, 0), st_makepoint(10, 10)));
->  ERROR:  Geometry SRID (0) does not match column SRID (25832)

insert into "ttt" values(1000, st_setsrid(st_makeline(st_makepoint(0, 0), st_makepoint(10, 10)), -1));
->  NOTICE:  SRID value -1 converted to the officially unknown SRID value 0
->  ERROR:  Geometry SRID (0) does not match column SRID (25832)
IMHO it is better to fix the data on the server. How does other software (QGIS) handle your table?
Nov 7, 2013 at 3:11 PM
I made myself smart about how the data came into PostGIS with SRID=-1. There was a shapefile imported into PostGIS. Shapefiles do not carry SRIDs.

On the screen "Shape File to PostGIS Importer" in the "Configuration" section you find SRID as -1. If you don't overwrite that, every object is imported with SRID -1.
How does other software (QGIS) handle your table?
Manifold shows every object, with SRID 25832 or -1. -1 isn't interpreted as not matching. If SharpMap would behave like that, great!

For the time being, i'm glad when there's no error raised by SRID -1...
Coordinator
Nov 8, 2013 at 12:53 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.