Hi there,
Geoserver 1.5 / 1.5 RC3
Oracle 10.2.0.2
uDig 1.1-RC10
I am trying to edit spatial columns in Oracle tables through WFS in geoserver and have come across some problems.
Initially I got this working with uDig by doing the following:
create table in oracle with spatial column and numeric primary key, sequence and trigger.
create datastore in geoserver pointing to oracle schema
create featuretype in geoserver pointing to new table
load WFS layer into uDig
edit
This worked with the only exception being that when drawing polygons some of them would not be completely filled when saved e.g. a half filled triangle. The edge of the fill lines seemed to line up and I suspected they were outside the co-ordinate bounds of the map (SRID 27700 British National Grid) so to identify that I attempted to load in a dataset containing county boundaries in the UK. I loaded this into Oracle using MapInfo’s Easyloader utility and then attempted to set it up in Geoserver but it didn’t work. Clicking the “LookupSRS” button in featuretype editor gave the following error:
SRS WKT: Could not find a definition for: EPSG:UNKNOWN
The output in the geoserver window included the following:
3115152 [WARNING] org.geotools.data.oracle.OracleDataStore - Could not map SRID
27700 to CRS:java.io.IOException: Unabled to parse WKTEXT into a CRS:PROJCS["OSG
B 1936 / British National Grid", GEOGCS [ "OSGB 1936", DATUM ["OSGB 1936 (EPSG I
D 6277)", SPHEROID ["Airy 1830 (EPSG ID 7001)",
6377563.396, 299.3249646], 446.4
48, -125.157, 542.06, .1499999999999998453747986861315636107214, .24699999999999
9745383835169829974745654, .841999999999999132037203291485177068183, 1.000020489
], PRIMEM [ "Greenwich",
0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328
]], PROJECTION ["British National Grid (EPSG OP 19916)"], PARAMETER ["Latitude_O
f_Origin", 49], PARAMETER ["Central_Meridian", -2], PARAMETER ["Scale_Factor", .
9996012717], PARAMETER ["False_Easting", 400000], PARAMETER ["False_Northing", -
100000], UNIT ["Meter", 1]]
I then tried a few things to remedy it
Change the SRID in the database table (Easyloader set it to 81989) to 27700, update the user_sdo_geom_metadata view.
Add value for 27700 (as above) to espg.properties file as per: http://docs.codehaus.org/display/GEOSDOC/Custom+projection+definition+in+Geoserver+1.5.0+%28onwards%29
creating alias for cs_srs as per http://jira.codehaus.org/browse/GEOT-826 (this is for when the view cannot be found not the case here)
Checking the value which had been saved against the first table which worked revealed that it was different to the one in the error messages (and the database)
Direct from database (and in error message):
PROJCS[“OSGB 1936 / British National Grid”, GEOGCS [ “OSGB 1936”, DATUM [“OSGB 1936 (EPSG ID 6277)”, SPHEROID [“Airy 1830 (EPSG ID 7001)”, 6377563.396, 299.3249646], 446.448, -125.157, 542.06, .1499999999999998453747986861315636107214, .246999999999999745383835169829974745654, .841999999999999132037203291485177068183, 1.000020489], PRIMEM [ “Greenwich”, 0.000000 ], UNIT [“Decimal Degree”, 0.01745329251994328]], PROJECTION [“British National Grid (EPSG OP 19916)”], PARAMETER [“Latitude_Of_Origin”, 49], PARAMETER [“Central_Meridian”, -2], PARAMETER [“Scale_Factor”, .9996012717], PARAMETER [“False_Easting”, 400000], PARAMETER [“False_Northing”, -100000], UNIT [“Meter”, 1]]
Logged against TBLWFSTEST27700 in Geoserver on devserver (the table that worked):
PROJCS[“OSGB 1936 / British National Grid”, GEOGCS[“OSGB 1936”, DATUM[“OSGB 1936”, SPHEROID[“Airy 1830”, 6377563.396, 299.3249646, AUTHORITY[“EPSG”,“7001”]], TOWGS84[446.448, -125.157, 542.06, 0.15, 0.247, 0.842, -4.2261596151967575], AUTHORITY[“EPSG”,“6277”]], PRIMEM[“Greenwich”, 0.0, AUTHORITY[“EPSG”,“8901”]], UNIT[“degree”, 0.017453292519943295], AXIS[“Geodetic longitude”, EAST], AXIS[“Geodetic latitude”, NORTH], AUTHORITY[“EPSG”,“4277”]], PROJECTION[“Transverse Mercator”, AUTHORITY[“EPSG”,“9807”]], PARAMETER[“central_meridian”, -2.0], PARAMETER[“latitude_of_origin”, 49.0], PARAMETER[“scale_factor”, 0.9996012717], PARAMETER[“false_easting”, 400000.0], PARAMETER[“false_northing”, -100000.0], UNIT[“m”, 1.0], AXIS[“Easting”, EAST], AXIS[“Northing”, NORTH], AUTHORITY[“EPSG”,“27700”]]
Changing the value in mdsys.sdo_cs_srs table to the value which worked fixed this particular issue but when loading the tables in uDig they now both failed with the error:
Error: Problem rendering: Exception rendering layer DefaultMapLayer[Test, VISIBLE, style=StyleImpl[ name=Default Styler], data=net.refractions.udig.project
.internal.impl.UDIGFeatureStore@anonymised.com, query=Query: [Request All Features]
Changing it back fixes the error in uDig and the original layer loads fine and I can edit etc. but the new layer (the county layer) doesn’t work still, it loads in uDig but does not render.
Suspicions turned to easyloader screwing up the cs_srs table so I tried the whole thing again on a fresh Oracle 10.2.0.2, geoserver 1.5 and uDig install. Setup the table in Oracle:
CREATE TABLE "MASTERMAP"."TBLWFSTEST27700"
( "WFSTEST27700ID" NUMBER, "WFSTEST27700GEOMETRY" MDSYS.SDO_GEOMETRY,
CONSTRAINT "WFSTEST27700_PK" PRIMARY KEY ("WFSTEST27700ID") VALIDATE )
TABLESPACE "MASTERMAP";
CREATE SEQUENCE "MASTERMAP"."TBLWFSTESTAUTON" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER NOCYCLE ;
create or replace TRIGGER "MASTERMAP"."TBLWFSTEST27700AUTONTRIG" BEFORE
INSERT ON "MASTERMAP"."TBLWFSTEST27700" FOR EACH ROW begin
if :new.wfstest27700id is null then
select mastermap.tblwfstestauton.nextval into :new.wfstest27700id from dual;
end if;
end;
/
INSERT INTO USER_SDO_GEOM_METADATA
VALUES ( 'TBLWFSTEST27700', 'WFSTEST27700GEOMETRY',
MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('X', 0.0, 2000000.0, 0.0050 ),MDSYS.SDO_DIM_ELEMENT('Y', 0.0, 2000000.0, 0.0050 ) ),
27700 );
CREATE INDEX "MASTERMAP"."TBLWFSTEST27700_SX" ON "MASTERMAP"."TBLWFSTEST27700" ("WFSTEST27700GEOMETRY")
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS (' LAYER_GTYPE=COLLECTION SDO_RTR_PCTFREE=10 INITIAL=64K ');
Then add oracle datastore, then add featuretype but it fails when clicking “lookup SRS” exactly the same as before with the error:
3115152 [WARNING] org.geotools.data.oracle.OracleDataStore - Could not map SRID
27700 to CRS:java.io.IOException: Unabled to parse WKTEXT into a CRS:PROJCS["OSG
B 1936 / British National Grid", GEOGCS [ "OSGB 1936", DATUM ["OSGB 1936 (EPSG I
D 6277)", SPHEROID ["Airy 1830 (EPSG ID 7001)", 6377563.396, 299.3249646], 446.4
48, -125.157, 542.06, .1499999999999998453747986861315636107214, .24699999999999
9745383835169829974745654, .841999999999999132037203291485177068183,
1.000020489
], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328
]], PROJECTION ["British National Grid (EPSG OP 19916)"], PARAMETER ["Latitude_O
f_Origin", 49], PARAMETER ["Central_Meridian", -2], PARAMETER ["Scale_Factor", .
9996012717], PARAMETER ["False_Easting", 400000], PARAMETER ["False_Northing", -
100000], UNIT ["Meter", 1]]
I have also tried this in Geoserver 1.5 RC3 and tried using each of the
gt2-oracle-spatial-2.2.2-SNAPSHOT.jar
I have also tried this in Geoserver 1.5 RC3 and tried using each of the gt2-oracle-spatial-2.2.2-SNAPSHOT.jar and gt2-oracle-spatial-2.3.1.jar but none makes a difference.
Does anyone have a clue what’s going on here? Can anyone reproduce it? Where did Geoserver get the SRID code from the very first time I setup a table (when it worked) since I can’t see that I’m doing anything different this time around. Any clues at all would be greatly appreciated.
cheers,
Tom