[GIS] Using CURRENT_DATE function on ArcSDE SQL Server Database

arcgis-desktoparcmapenterprise-geodatabasesql server

I am writing a script to access data from each day. I would like to select by attribute and send these features to a local file geodatabase. After reading some documenation, CURRENT_DATE is the function which reads data for today.

This is an example of my data '2015-02-02 00:00:00'. I don't have the time for any features, so in reality it is only YYYY-MM-DD. In a local file geodatabase, the query

PlannedDate = CURRENT_DATE works. However in my ArcSDE SQL Server connection it does not.

enter image description here

Best Answer

This function requires SQL Server Syntax for the mm/dd/yyyy format.

PlannedDate = CONVERT(DATE, GETDATE())

Related Solutions

[GIS] Can Mapnik render spatial data from SQL Server

There seems to be a lack of a clear overview on the OGR Virtual Format help page. The phrase it can "provide an anchor file for access to non-file oriented datasources" seems to imply any connection type can be used, but I'm not sure this is the case.

Its original function was to allow spatial data to be created from non-spatial sources, for example by combining an X and Y column in a database table to create point features.

However there is no reason why a spatial database can't also be used for this purpose, but you should then use a standard ODBC connection rather than the MSSQL driver.

To do this create a DSN (Data Source Name) in Windows. Then use the ODBC connection and the DSN in the virtual layer configuration. So if your DSN is named OSVectorMap:

<OGRVRTDataSource>
  <OGRVRTLayer name="AASQLlayer">
    <SrcDataSource>ODBC:username/password@OSVectorMap</SrcDataSource>
    <SrcSQL>SELECT geom27700.STAsText() AS geomWKT FROM TG11_Settlement_Area</SrcSQL>
    <GeometryField encoding="WKT" field="geomWKT"/>
    <LayerSRS>EPSG:4326</LayerSRS>
    <GeometryType>wkbPolygon</GeometryType>
  </OGRVRTLayer>
</OGRVRTDataSource>

There are a few more details on creation of Virtual Layers in the MapServer wiki and Virtual Spatial Data page.

Great blog post by the way. I wouldn't worry too much about using Python 3.x - nearly all the useful GIS libraries are still in 2.x And the SQL Server Geometry and Geography types are great. The only downsides are that SQL Server unfortunately seems a second class citizen in the opensource world in terms of blog posts, and integration with other tools.

[GIS] Adding data from a SQL Server Spatial database to ArcMap or ArcEngine application with ArcObjects for better performance

7 million is not that big a number of records. The relationship classes are probably what are killing you. I would suggest two things. 1) For editing, use small area child replicas. This will greatly reduce the number of records needed while still giving you the capabilities you need from your relationship classes. 2) For viewing, start with a non-versioned read-only replica for viewing only. If that still is too poor of performance, then switch to a denormalized view. If this is a regularly edited database, the denormalized view will be a little trickier to maintain, but you will have dramatically improved performance at the cost of space. You might even be able to do spatially limited view only replicas like I recommended for editing, depending on the use pattern for your database. (Also, make sure your view-only clients are using feature caching.)

Best Answer

Related Solutions

[GIS] Can Mapnik render spatial data from SQL Server

[GIS] Adding data from a SQL Server Spatial database to ArcMap or ArcEngine application with ArcObjects for better performance

Related Question