Re: [mapserver-users] OGRVRT Data Sources and SQL server
Hi Robert and Jeff I tried those steps (think FID is optional) and no further - more blank outputs using shp2img (ms bebugging at 5 - no errors at all, happily finds layer and draws it - apparently! - should rename my layer to snowflakes or white cats). Have run shp2img debugging - nothing obvious, but couldn't print out output (can send screenshot if anyone interested). Not sure how to retrieve the sql query sent (Jeff?). I have grabbed the output from ogrinfo (see below - no extents...does that mean something) - seems to be working ok to the untrained eye! Mapfile below too (NB. have tried with and without projections at map and layer level), I am baffled, will start to use a later version of ms4w and check on another server too. Cheers Chris ogrinfo output: INFO: Open of `d:/xxx/xxx/omreg2.ovf' using driver `VRT' successful. Layer name: omreg1 Geometry: Point Feature Count: 5 Layer SRS WKT: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4326"]] SiteKeyID: Integer (10.0) SiteID: Integer (10.0) SiteType: String (255.0) SiteName: String (255.0) SiteLat: Real (0.0) SiteLong: Real (0.0) OGRFeature(omreg1):1 SiteKeyID (Integer) = 2269 SiteID (Integer) = 1 SiteType (String) = Regulated tidal exchange SiteName (String) = Abbotts Hall (Stage 1) SiteLat (Real) = 0.84556009184722 SiteLong (Real) = 51.7846475991705 POINT (51.784647599170498 0.84556009184722) My mapfile now is this: MAP UNITS meters EXTENT -15 30 40 70 SIZE 600 500 SHAPEPATH "d:\xxx\xxx\" SYMBOLSET "../common/symbols/symbols-pmapper.sym" FONTSET "../common/fonts/msfontset.txt" RESOLUTION 96 IMAGETYPE png INTERLACE OFF CONFIG "PROJ_LIB" "D:\xxx\proj\nad\" CONFIG "GDAL_DATA" "D:\xxx\gdaldata\" CONFIG "MS_ERRORFILE" "D:\x\tmp\ms_error.txt" DEBUG 5 PROJECTION #"init=epsg:4326" "+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs no_defs" END # # Image formates for GD # OUTPUTFORMAT NAME "png" DRIVER "GD/PNG" MIMETYPE "image/png" IMAGEMODE RGB FORMATOPTION INTERLACE=OFF TRANSPARENT OFF EXTENSION "png" END OUTPUTFORMAT NAME "png8" DRIVER "GD/PNG" MIMETYPE "image/png" IMAGEMODE PC256 FORMATOPTION INTERLACE=OFF TRANSPARENT OFF EXTENSION "png" END OUTPUTFORMAT NAME "jpeg" DRIVER "GD/JPEG" MIMETYPE "image/jpeg" IMAGEMODE RGB FORMATOPTION "QUALITY=70" EXTENSION "jpg" END OUTPUTFORMAT NAME GTiff DRIVER "GDAL/GTiff" MIMETYPE "image/tiff" IMAGEMODE RGB FORMATOPTION "TFW=YES" #FORMATOPTION "COMPRESS=PACKBITS" EXTENSION "tif" END OUTPUTFORMAT NAME imagemap MIMETYPE "text/html" FORMATOPTION SKIPENDTAG=OFF DRIVER imagemap END LAYER NAME "test" TYPE POINT CONNECTIONTYPE OGR CONNECTION "omreg2.ovf" # have also tried ./omreg2.ovf - both files in same folder - no luck with either DATA "omreg1" PROJECTION #"init=epsg:4326" "+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs no_defs" END STATUS ON CLASS SYMBOL 1 SIZE 6 COLOR 255 0 0 LABEL SIZE MEDIUM TYPE BITMAP COLOR 0 0 0 END END END END #Map On 29 May 2012 21:54, Robert Sanson wrote: > Hi Chris > > You need to define the primary key in your .ovf file: > > farmgate_id > > I put the .ovf file in the same directory as my .map file, then Mapserver > can find it: > > CONNECTION "./farmgates.ovf" > > All thebest, > > Robert > > > ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] OGRVRT Data Sources and SQL server
Hi Chris You need to define the primary key in your .ovf file: farmgate_id I put the .ovf file in the same directory as my .map file, then Mapserver can find it: CONNECTION "./farmgates.ovf" All thebest, Robert >>> Chris Jackson 29/05/2012 8:57 p.m. >>> Hi Robert Thanks for the reply, you have got me to a point where ogrinfo commandline processes an ovf file using sql authentication fine. However it will still not display using shp2img commandline (other layer in mapfile draws fine). My layer definition is fairly simple, but I am scratching my head to think what is stopping the layer from drawing! The points are in standard lat/long so using WGS84 (layer and map projection). Cheers Chris ovf contains ODBC:user/xxx@conn SELECT * FROM dbo.Site wkbPoint EPSG:4326 Layer defined as: LAYER NAME "test" TYPE POINT CONNECTIONTYPE OGR CONNECTION "omreg.ovf" # the name and path to the virtual.ovf file, relative to shapepath may work? DATA "omreg1" # the name of the OGRVrtLayer STATUS ON LABELITEM "SiteName" #may need to use table.name or just name depending on your OVF file. PROJECTION "init=epsg:4326" END CLASS SYMBOL 1 SIZE 6 COLOR 255 0 0 LABEL SIZE MEDIUM TYPE BITMAP COLOR 0 0 0 END END END This email and any attachments are confidential and intended solely for the addressee(s). If you are not the intended recipient, please notify us immediately and then delete this email from your system. This message has been scanned for Malware and Viruses by Websense Hosted Security. www.websense.com ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] OGRVRT Data Sources and SQL server
On 12-05-29 8:40 AM, Jeff McKenna wrote: > On 12-05-29 5:57 AM, Chris Jackson wrote: >> Hi Robert >> >> Thanks for the reply, you have got me to a point where ogrinfo >> commandline processes an ovf file using sql authentication fine. >> However it will still not display using shp2img commandline (other layer >> in mapfile draws fine). My layer definition is fairly simple, but I am >> scratching my head to think what is stopping the layer from drawing! >> The points are in standard lat/long so using WGS84 (layer and map >> projection). > > Hmm tricky. I would then: > > - remove all layers from my mapfile, leaving only the one problem layer > - remove all PROJECTION objects > - then I would execute ogrinfo on my ovf file > - and use the extents returned there in the EXTENT parameter of my > mapfile. > > Worth a shot. Then finally, if I can't get that to work, I would try to get the exact command/sql being sent to my database, through enabling debugging for that layer (see http://www.mapserver.org/optimization/debugging.html). Sometimes just seeing the sql will point to a problem. Hope that all helps. -jeff -- Jeff McKenna MapServer Consulting and Training Services http://www.gatewaygeomatics.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] OGRVRT Data Sources and SQL server
On 12-05-29 5:57 AM, Chris Jackson wrote: > Hi Robert > > Thanks for the reply, you have got me to a point where ogrinfo > commandline processes an ovf file using sql authentication fine. > However it will still not display using shp2img commandline (other layer > in mapfile draws fine). My layer definition is fairly simple, but I am > scratching my head to think what is stopping the layer from drawing! > The points are in standard lat/long so using WGS84 (layer and map > projection). Hmm tricky. I would then: - remove all layers from my mapfile, leaving only the one problem layer - remove all PROJECTION objects - then I would execute ogrinfo on my ovf file - and use the extents returned there in the EXTENT parameter of my mapfile. Worth a shot. -jeff -- Jeff McKenna MapServer Consulting and Training Services http://www.gatewaygeomatics.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] OGRVRT Data Sources and SQL server
Hi Robert Thanks for the reply, you have got me to a point where ogrinfo commandline processes an ovf file using sql authentication fine. However it will still not display using shp2img commandline (other layer in mapfile draws fine). My layer definition is fairly simple, but I am scratching my head to think what is stopping the layer from drawing! The points are in standard lat/long so using WGS84 (layer and map projection). Cheers Chris ovf contains ODBC:user/xxx@conn SELECT * FROM dbo.Site wkbPoint EPSG:4326 Layer defined as: LAYER NAME "test" TYPE POINT CONNECTIONTYPE OGR CONNECTION "omreg.ovf" # the name and path to the virtual.ovf file, relative to shapepath may work? DATA "omreg1" # the name of the OGRVrtLayer STATUS ON LABELITEM "SiteName" #may need to use table.name or just name depending on your OVF file. PROJECTION "init=epsg:4326" END CLASS SYMBOL 1 SIZE 6 COLOR 255 0 0 LABEL SIZE MEDIUM TYPE BITMAP COLOR 0 0 0 END END END ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] OGRVRT Data Sources and SQL server
Hi Chris I am successfully using OGR with SQL-Server with SQL authentication. This is what I do: Say I want to plot farm gates. I have a System DSN datasource set up in ODBC say called "MYDSN" with SQL Server authentication. I create a farmgates.ovf file containing: ODBC:user/passwd@MYDSN vw_farmgates wkbPoint EPSG:2193 point_location_id In my map file I have: LAYER NAME "farmgates" GROUP "FMS" CONNECTION "./farmgates.ovf" CONNECTIONTYPE OGR DATA "farmgates" #FILTER "WHERE feature_type_id = 11 and farm_id = '%farmid%'" SIZEUNITS PIXELS STATUS ON DUMP TRUE # query templates for HTML-based queries # responds to OGC:WMS request=GetFeatureInfo..&info_format=text/html HEADER ../templates/farmgates_query_header.html TEMPLATE ../templates/farmgates_query_body.html # fuzziness for querying TOLERANCE 5000 TOLERANCEUNITS METERS TYPE POINT UNITS METERS # scale range MAXSCALE 25 MINSCALE 750 LABELMAXSCALE 10 LABELMINSCALE 1 LABELCACHE on LABELITEM 'reference_code' CLASS STYLE COLOR 138 43 226 OUTLINECOLOR 0 0 0 SIZE 13 SYMBOL "triangle" END LABEL TYPE truetype FONT "arial" SIZE 10 POSITION ur #ANGLE auto OUTLINECOLOR 235 235 235 #--Halo COLOR 0 0 0 #MINDISTANCE 50 #MINFEATURESIZE 10 BUFFER 2 #SIZE small END #LABEL END #CLASS METADATA "wms_group_title" "FMS" "wms_name""farmgates" "wms_title""farmgates" "wms_abstract""These are farmgates in NZTM" "wms_keywordlist""AgriBase" #"wms_server_version""1.1.1" "wms_srs" "EPSG:2193" "wms_extent" "1114412.940 4793178.168 2089186.204 6190213.601" "wms_dataurl_format" "text/HTML" "gml_include_items" "all" "gml_exclude_items" "" "ows_include_items" "all" "ows_exclude_items" "" "wms_exceptions_format" "application/vnd.ogc.se_xml" END END #farmgates Regards, Robert >>> Chris Jackson 29/05/2012 3:01 a.m. >>> Hi all Has anyone got much experience of using OGRVRT Data Sources in Windows with SQL server system DSNs? I am having an issue with system DSN connection type (windows authentication will work fine and proceses the table fine, sql authentication - get not trusted connection (both authentication types enabled in instance)) when testing via ogrinfo commandline. Then if I take the working code forward then I can't render the layer in a map output via say shp2img commandline or final map application. Map code below: LAYER NAME "virtual" TYPE POINT CONNECTIONTYPE OGR CONNECTION " ODBC:@omreg1 SELECT * FROM dbo.Site wkbPoint " # the name and path to the virtual.ovf file, relative to shapepath may work? DATA "omreg1" TEMPLATE void PROJECTION "init=epsg:4326" END METADATA "wms_srs" "4326" "wms_title" "virtual" END SIZEUNITS PIXELS STATUS ON TOLERANCE 0 TOLERANCEUNITS PIXELS UNITS METERS CLASS NAME "Virtual" COLOR 255 0 0 OUTLINECOLOR 255 0 0 SYMBOL 'kreuz4' SIZE 6 END # Class END My version of mapserver is a little old (5.6.1). I wonder whether I need to set any extra SQL related env variables. Any assistance much appreciated! Thanks Chris This email and any attachments are confidential and intended solely for the addressee(s). If you are not the intended recipient, please notify us immediately and then delete this email from your system. This message has been scanned for Malware and Viruses by Websense Hosted Security. www.websense.com ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
