Has anyone tried creating a spatial view on an XY table in MS SQL Server,
so it can be exposed via GeoServer?
We are not having any luck with this. What we have done is:
1) Define a spatial view:
CREATE VIEW UDO_Fuel_Cache_SVW AS
SELECT
ID,
Latitude,
Longitude,
Geographic,
Fuel_Type,
Updated,
Full_Barrels,
Partial_Barrels,
Empty_Barrels,
Owner,
ContactPhone,
Comments,
FireCentre,
FireZone,
geography::STGeomFromText('POINT('+convert(varchar(20),Longitude)+' '+
convert(varchar(20),Latitude)+')',4326) AS Geom
FROM Incident.dbo.UDO_Fuel_Cache
2) Define a geometry metadata table:
CREATE TABLE GEOMETRY_COLUMNS(
F_TABLE_SCHEMA VARCHAR(30) NOT NULL,
F_TABLE_NAME VARCHAR(30) NOT NULL,
F_GEOMETRY_COLUMN VARCHAR(30) NOT NULL,
COORD_DIMENSION INTEGER,
SRID INTEGER NOT NULL,
TYPE VARCHAR(30) NOT NULL,
UNIQUE(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN),
CHECK(TYPE IN ('POINT','LINE', 'POLYGON', 'COLLECTION', 'MULTIPOINT',
'MULTILINE', 'MULTIPOLYGON', 'GEOMETRY') ));
go
INSERT INTO GEOMETRY_COLUMNS
( F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID,
TYPE )
VALUES ('', 'UDO_Fuel_Cache_SVW', 'Geom', 2, 4326, 'POINT');
3) Configure the SQL Server Store to refer to GEOMETRY_COLUMNS
4) Configure the layer (I hope correctly). The layer featuretype listing
shows a column Geom of type byte[] - which doesn't seem promising.
When trying to view the layer, we get the error:
2017-02-06 09:44:47,833 ERROR [org.geotools.jdbc] - Failed to execute
statement SELECT FROM "UDO_Fuel_Cache_SVW"
Caused by: java.sql.SQLException:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'FROM'.
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
