[mapserver-users] OGR and MSSQL non-specific error

[Anzel, Phil - NRCS, Fort Collins, CO]

Hi, Tamas,

I believe there is at least one bug in the OGR code: the SRID is not properly retrieved from the underlying Sql Server 2008 database or its value is ignored, and therefore zero records are returned.

Consider this Layer definition:
  LAYER
    NAME 'States'    STATUS ON
    PROJECTION 'proj=latlong' 'ellps=GRS80' 'datum=NAD83' END
    CONNECTIONTYPE OGR
    CONNECTION 'MSSQL:server=xxx;database=Navigation;uid=yyy;pwd=zzz;tables=NAV_STATE(ShapeGeometry)'
    PROCESSING 'CLOSE_CONNECTION=DEFER'
    DATA 'NAV_STATE'
    TYPE POLYGON
    CLASS
      STYLE
        OUTLINECOLOR '#e1dd00'
        WIDTH 2
      END
    END
  END

The ShapeGeometry has a non-zero SRID, as evidenced by a query executed against the database via Sql Server Management Studio (SSMS):
   select top 1 ShapeGeometry.STSrid from dbo.NAV_STATE
    -- that returns 4269

Using SSMS’ Activity Monitor, executing SHP2IMG with a mapfile containing the above LAYER causes the following query to be issued to Sql Server:
  select
    [OBJECTID], [ShapeGeometry], [AREA],
    [STATE_NAME], [STATE_FIPS], [SUB_REGION], [STATE_ABBR],
    [POP1990], [POP1997], [POP90_SQMI], [HOUSEHOLDS], [MALES],
    [FEMALES], [WHITE], [BLACK], [AMERI_ES], [ASIAN_PI], [OTHER],
    [HISPANIC], [AGE_UNDER5], [AGE_5_17], [AGE_18_29], [AGE_30_49],
    [AGE_50_64], [AGE_65_UP], [NEVERMARRY], [MARRIED], [SEPARATED],
    [WIDOWED], [DIVORCED], [HSEHLD_1_M], [HSEHLD_1_F], [MARHH_CHD],
    [MARHH_NO_C], [MHH_CHILD], [FHH_CHILD], [HSE_UNITS], [VACANT],
    [OWNER_OCC], [RENTER_OCC], [MEDIAN_VAL], [MEDIANRENT], [UNITS_1DET],
    [UNITS_1ATT], [UNITS2], [UNITS3_9], [UNITS10_49], [UNITS50_UP],
    [MOBILEHOME], [NO_FARMS87], [AVG_SIZE87], [CROP_ACR87],
    [AVG_SALE87]
  from dbo.NAV_STATE
  where [ShapeGeometry].STIntersects(
    geometry::STGeomFromText(
     'POLYGON((-126 22.1993540334789,
               -66 22.1993540334789,
               -66 51.1734146762311,
               -126 51.1734146762311,-126 22.1993540334789))',0)) = 1

Note that the STIntersects function is given an SRID argument of 0. This results in a return of zero records.

I also note that all columns are returned. That is unfortunate, as that could potentially represent quite a bit of network traffic,

- Phil

From: Tamas Szekeres [mailto:szekerest at gmail.com]
Sent: Monday, February 27, 2012 2:59 PM
To: Anzel, Phil - NRCS, Fort Collins, CO
Cc: mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] OGR and MSSQL non-specific error


2012/2/26 Anzel, Phil - NRCS, Fort Collins, CO <Phil.Anzel at ftc.usda.gov<mailto:Phil.Anzel at ftc.usda.gov>>

1. Note that I omitted the “tables=…” as it appears that naming a specific table would interfere with connection pooling if I’m using many layers drawing data from different tables. Do I understand the role of the “tables=…” incorrectly?


Yes, it may be considered as being a different connection if the tables section is used.


2. I added the explicit spatial filtering (STIntersects…). Is there a better way to do this?


It seems to be a bit complicated this way. I think you should try simply setting the layer name in the DATA section. In this case the spatial filter is added automatically by the MapServer OGR driver.

Best regards,

Tamas

This electronic message contains information generated by the USDA solely for the intended recipients. Any unauthorized interception of this message or the use or disclosure of the information it contains may violate the law and subject the violator to civil or criminal penalties. If you believe you have received this message in error, please notify the sender and delete the email immediately.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20120228/a147cf24/attachment.htm>