[Geoserver-devel] [jira] Created: (GEOS-4507) WFS Getfeature fails on MSSQL data sources that are created from server side views

_Failed_to_Retrieve · April 25, 2011, 12:22pm

WFS Getfeature fails on MSSQL data sources that are created from server side views
----------------------------------------------------------------------------------

                 Key: GEOS-4507
                 URL: http://jira.codehaus.org/browse/GEOS-4507
             Project: GeoServer
          Issue Type: Bug
          Components: SQLServer, WFS
    Affects Versions: 2.1-RC3
         Environment: Geoserver 2.1-RC3, Ubuntu 10.4, OpenJDK6, microsoft jdbc 3.0
            Reporter: Siim Aus
            Assignee: Andrea Aime

When layer is created from MSSQL table that have primary key, for example

CREATE TABLE [dbo].[WaterPipeSegments](
  [SEGMENT_MSLINK] [int] NOT NULL,
  [SUBSEGMENT_MSLINK] [int] NOT NULL,
  [SEGMENT_TYPE] [int] NOT NULL,
  [INLET] [float] NULL,
  [OUTLET] [float] NULL,
  [START_MANHOLE_MSLINK] [int] NOT NULL,
  [END_MANHOLE_MSLINK] [int] NOT NULL,
  [PIPE_LENGTH_3D] [float] NULL,
  [PIPE_LENGTH_2D] [float] NULL,
  [NETWORK] [int] NULL,
  [MATERIAL] [char](10) NOT NULL,
  [DIAMETER] [varchar](50) NOT NULL,
  [HOLDER] [char](10) NOT NULL,
  [LINETYPE] [int] NULL,
  [ACCURACY] [int] NULL,
  [PRESSURE] [int] NOT NULL,
  [YEAR] [int] NOT NULL,
  [EXISTING] [int] NOT NULL,
  [OPERATING] [int] NOT NULL,
  [UPDATED] [smalldatetime] NULL,
  [INSERTED] [smalldatetime] NULL,
  [SEGMENT_GEOMETRY] [geometry] NULL,
PRIMARY KEY CLUSTERED
(
  [SUBSEGMENT_MSLINK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and layer is created from that view, then DescribeFeatureType request from WFS server gives following structure:

http://dev-geoserver:8080/geoserver/ows?service=WFS&version=1.0.0&request=DescribeFeatureType&typename=astv:WaterPipeSegments

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:aa="aa" xmlns:astv="http://www.tallinnavesi.ee" xmlns:cite="http://www.opengeospatial.net/cite" xmlns:gml="http://www.opengis.net/gml" xmlns:it.geosolutions="http://www.geo-solutions.it" xmlns:nurc="http://www.nurc.nato.int" xmlns:nyc_roads="http://dev-geoserver/geoserver/nyc_roads" xmlns:sde="http://geoserver.sf.net" xmlns:sf="http://www.openplans.org/spearfish" xmlns:t="t" xmlns:tiger="http://www.census.gov" xmlns:topp="http://www.openplans.org/topp" elementFormDefault="qualified" targetNamespace="http://www.tallinnavesi.ee">
  <xsd:import namespace="http://www.opengis.net/gml" schemaLocation="http://dev-geoserver:8080/geoserver/schemas/gml/2.1.2/feature.xsd"/>
  <xsd:complexType name="WaterPipeSegmentsType">
    <xsd:complexContent>
      <xsd:extension base="gml:AbstractFeatureType">
        <xsd:sequence>
          <xsd:element maxOccurs="1" minOccurs="1" name="SEGMENT_MSLINK" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="SUBSEGMENT_MSLINK" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="SEGMENT_TYPE" nillable="false" type="xsd:int"/>

          <xsd:element maxOccurs="1" minOccurs="0" name="INLET" nillable="true" type="xsd:double"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="OUTLET" nillable="true" type="xsd:double"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="START_MANHOLE_MSLINK" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="END_MANHOLE_MSLINK" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="PIPE_LENGTH_3D" nillable="true" type="xsd:double"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="PIPE_LENGTH_2D" nillable="true" type="xsd:double"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="NETWORK" nillable="true" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="MATERIAL" nillable="false" type="xsd:string"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="DIAMETER" nillable="false" type="xsd:string"/>

          <xsd:element maxOccurs="1" minOccurs="1" name="HOLDER" nillable="false" type="xsd:string"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="LINETYPE" nillable="true" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="ACCURACY" nillable="true" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="PRESSURE" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="YEAR" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="EXISTING" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="1" name="OPERATING" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="UPDATED" nillable="true" type="xsd:dateTime"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="INSERTED" nillable="true" type="xsd:dateTime"/>

          <xsd:element maxOccurs="1" minOccurs="0" name="SEGMENT_GEOMETRY" nillable="true" type="gml:GeometryPropertyType"/>
        </xsd:sequence>
      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:element name="WaterPipeSegments" substitutionGroup="gml:_Feature" type="astv:WaterPipeSegmentsType"/>
</xsd:schema>

and requesting single feature with http://dev-geoserver:8080/geoserver/ows?service=WFS&version=1.0.0&request=GetFeature&featureid=WaterPipeSegments.683

<?xml version="1.0" encoding="UTF-8"?><wfs:FeatureCollection xmlns="http://www.opengis.net/wfs" xmlns:wfs="http://www.opengis.net/wfs" xmlns:astv="http://www.tallinnavesi.ee" xmlns:gml="http://www.opengis.net/gml" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.tallinnavesi.ee http://dev-geoserver:8080/geoserver/wfs?service=WFS&version=1.0.0&request=DescribeFeatureType&typeName=astv%3AWaterPipeSegments http://www.opengis.net/wfs http://dev-geoserver:8080/geoserver/schemas/wfs/1.0.0/WFS-basic.xsd"><gml:boundedBy><gml:null>unknown</gml:null></gml:boundedBy><gml:featureMember><astv:WaterPipeSegments fid="WaterPipeSegments.683"><astv:SEGMENT_MSLINK>9956</astv:SEGMENT_MSLINK><astv:SUBSEGMENT_MSLINK>683</astv:SUBSEGMENT_MSLINK><astv:SEGMENT_TYPE>2</astv:SEGMENT_TYPE><astv:INLET>15.66</astv:INLET><astv:OUTLET>15.67</astv:OUTLET><astv:START_MANHOLE_MSLINK>267356</astv:START_MANHOLE_MSLINK><astv:END_MANHOLE_MSLINK>257345</astv:END_MANHOLE_MSLINK><astv:PIPE_LENGTH_3D>9.391042947959427</astv:PIPE_LENGTH_3D><astv:PIPE_LENGTH_2D>9.390947610412734</astv:PIPE_LENGTH_2D><astv:NETWORK>3</astv:NETWORK><astv:MATERIAL>MLM </astv:MATERIAL><astv:DIAMETER>302</astv:DIAMETER><astv:HOLDER>TLV </astv:HOLDER><astv:LINETYPE>1</astv:LINETYPE><astv:ACCURACY>1</astv:ACCURACY><astv:PRESSURE>1</astv:PRESSURE><astv:YEAR>1992</astv:YEAR><astv:EXISTING>1</astv:EXISTING><astv:OPERATING>1</astv:OPERATING><astv:UPDATED>2005-05-11T14:44:00</astv:UPDATED><astv:INSERTED>2005-05-11T14:44:00</astv:INSERTED><astv:SEGMENT_GEOMETRY><gml:LineString srsName="http://www.opengis.net/gml/srs/epsg\.xml\#3301"><gml:coordinates xmlns:gml="http://www.opengis.net/gml" decimal="." cs="," ts=" ">542201.63881696,6588721.50107427 542201.5287678,6588720.91108404 542200.42868619,6588719.92117686</gml:coordinates></gml:LineString></astv:SEGMENT_GEOMETRY></astv:WaterPipeSegments></gml:featureMember></wfs:FeatureCollection>

However, when view is created from that table, like

create view v_WaterPipesSegmentsTest as
select [SEGMENT_MSLINK],[SEGMENT_GEOMETRY]
from [dbo].[WaterPipeSegments]

and layer from that then querying DescribeFeatureType gives us:
http://dev-geoserver:8080/geoserver/ows?service=WFS&version=1.0.0&request=DescribeFeatureType&typename=astv:v_WaterPipesSegmentsTest

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:aa="aa" xmlns:astv="http://www.tallinnavesi.ee" xmlns:cite="http://www.opengeospatial.net/cite" xmlns:gml="http://www.opengis.net/gml" xmlns:it.geosolutions="http://www.geo-solutions.it" xmlns:nurc="http://www.nurc.nato.int" xmlns:nyc_roads="http://dev-geoserver/geoserver/nyc_roads" xmlns:sde="http://geoserver.sf.net" xmlns:sf="http://www.openplans.org/spearfish" xmlns:t="t" xmlns:tiger="http://www.census.gov" xmlns:topp="http://www.openplans.org/topp" elementFormDefault="qualified" targetNamespace="http://www.tallinnavesi.ee">
  <xsd:import namespace="http://www.opengis.net/gml" schemaLocation="http://dev-geoserver:8080/geoserver/schemas/gml/2.1.2/feature.xsd"/>
  <xsd:complexType name="v_WaterPipesSegmentsTestType">
    <xsd:complexContent>
      <xsd:extension base="gml:AbstractFeatureType">
        <xsd:sequence>
          <xsd:element maxOccurs="1" minOccurs="1" name="SEGMENT_MSLINK" nillable="false" type="xsd:int"/>
          <xsd:element maxOccurs="1" minOccurs="0" name="SEGMENT_GEOMETRY" nillable="true" type="gml:GeometryPropertyType"/>
        </xsd:sequence>

      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:element name="v_WaterPipesSegmentsTest" substitutionGroup="gml:_Feature" type="astv:v_WaterPipesSegmentsTestType"/>
</xsd:schema>

When queried just features collection, all is normal:

http://dev-geoserver:8080/geoserver/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=astv:v_WaterPipesSegmentsTest&maxFeatures=1

Gives:

<?xml version="1.0" encoding="UTF-8"?><wfs:FeatureCollection xmlns="http://www.opengis.net/wfs" xmlns:wfs="http://www.opengis.net/wfs" xmlns:astv="http://www.tallinnavesi.ee" xmlns:gml="http://www.opengis.net/gml" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.tallinnavesi.ee http://dev-geoserver:8080/geoserver/wfs?service=WFS&version=1.0.0&request=DescribeFeatureType&typeName=astv%3Av_WaterPipesSegmentsTest http://www.opengis.net/wfs http://dev-geoserver:8080/geoserver/schemas/wfs/1.0.0/WFS-basic.xsd"><gml:boundedBy><gml:null>unknown</gml:null></gml:boundedBy><gml:featureMember><astv:v\_WaterPipesSegmentsTest fid="v_WaterPipesSegmentsTest.fid-3e558ce9_12f7416647b_fe6"><astv:SEGMENT_MSLINK>9956</astv:SEGMENT_MSLINK><astv:SEGMENT_GEOMETRY><gml:LineString srsName="http://www.opengis.net/gml/srs/epsg\.xml\#3301"><gml:coordinates xmlns:gml="http://www.opengis.net/gml" decimal="." cs="," ts=" ">542201.63881696,6588721.50107427 542201.5287678,6588720.91108404 542200.42868619,6588719.92117686</gml:coordinates></gml:LineString></astv:SEGMENT_GEOMETRY></astv:v_WaterPipesSegmentsTest></gml:featureMember></wfs:FeatureCollection>

However, when layes is queried using featureId as described in featuremember:

http://dev-geoserver:8080/geoserver/ows?service=WFS&version=1.0.0&request=GetFeature&featureid=v_WaterPipesSegmentsTest.fid-3e558ce9_12f7416647b_fe6

then feature cannot be found from layer, resulting empty dataset:

<?xml version="1.0" encoding="UTF-8"?><wfs:FeatureCollection xmlns="http://www.opengis.net/wfs" xmlns:wfs="http://www.opengis.net/wfs" xmlns:astv="http://www.tallinnavesi.ee" xmlns:gml="http://www.opengis.net/gml" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.tallinnavesi.ee http://dev-geoserver:8080/geoserver/wfs?service=WFS&version=1.0.0&request=DescribeFeatureType&typeName=astv%3Av_WaterPipesSegmentsTest http://www.opengis.net/wfs http://dev-geoserver:8080/geoserver/schemas/wfs/1.0.0/WFS-basic.xsd"><gml:boundedBy><gml:null>unknown</gml:null></gml:boundedBy></wfs:FeatureCollection>

I think this is not expected behaviour...I'm not tested this with orther data backends but this might exist for all JDBC data backends that are exposing structures as views...

Thanks, Siim

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira