[mapserver-users] OGR and MSSQL non-specific error
Anzel, Phil - NRCS, Fort Collins, CO
Phil.Anzel at ftc.usda.gov
Tue Feb 28 09:57:47 PST 2012
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:
NAME 'States' STATUS ON
PROJECTION 'proj=latlong' 'ellps=GRS80' 'datum=NAD83' 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:
[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],
-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,
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.
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...
More information about the mapserver-users