Ok, figured it out. The view as orginally defined used
"geography::STGeomFromText". GeoServer only recognizes the "geometry"
MS-SQL datatype (which is reasonable). Changing the view to use
"geometry::STGeomFromText" fixed the problem. GeoServer recognizes the
geometry column and renders it.
Furthermore, the GEOMETRY_COLUMNS metatable is not required, which is a
nice simplification.
Probably we should define a spatial functional index as well, but since
this is a Point layer with only a 100 or so records, performance is fine
even without it.
Another user reported that this even works with defining the view as a
GeoServer SQL View, which is a very handy capability. It would be nice if
this worked for Oracle as well!
On Mon, Feb 6, 2017 at 10:01 AM, Martin Davis <[email protected]> wrote:
> 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
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
