Vishful thinking…

ArcToSQL2008 – An ArcCatalog Command to export FeatureClasses to MS SQL SERVER 2008

Posted in ArcGIS, ESRI, GIS, SQL Server 2008, Utilities by viswaug on February 12, 2009

I had written this tool 3-4 months ago and it had been sitting on my machine waiting to be wrapped with an installer project. I finally got around to building a setup and deployment project for it and it is ready to distribute. The tool can be downloaded below

ArcToSQL2008

Once installed, you should be able to open the “Tools -> Customize” dialog in ArcCatalog

arctosql2008_1

Under the “GeoSpatial.NET” Categories, the “ArcToSQL2008” command should be available. Drag the command to any of the toolbars.

arctosql2008_2

Select the FeatureClass you want to export to the SQL Server 2008 database and click the “ArcToSQL2008” button on the toolbar. The dialog below should pop-up. Enter the name of the SQL Server 2008 database server and click connect. The Database drop-down will get populated with all available databases on the server. Select a database from the list and enter the name of the table that you want the FeatureClass exported to. Click OK. The FeatureClass will get exported and a message box should pop-up when the import is completed.

arctosql2008_3

Some highlights of the tool:

  • Exports FeatureClasses in a Projected Co-ordinate System as SQLGeometry in SQL 2008.
  • Exports FeatureClasses in a Geographic Co-ordinate System as SQLGeography in SQL 2008.
  • Any errors that occur during the export are logged in a log file found under in the install directory.
  • The tool is built using a broader GIS framework that should enable developers to customize the export process to their liking. I still have a little bit of house cleaning on the code for the broader GIS framework. I will post when it is all done. Hopefully that will be real soon. Contact me if you want to look at it sooner…

Things left to do:

  • Better UI showing the progress of the export operation.
  • More flexibility to the export operation by allowing the user to select the fields to be exported.
  • The export operation can be made faster by using the geometry builder classes to construct the SQLGeometry and SQLGeography objects. I took the shortcut for now and just parsed out the geometries from WKT strings.

Please let me know if you find any bugs in the tool using the “contact me” link on the right pane of this blog.

Advertisements

Filter Encoding Standard 1.1 and the curious case of the missing ‘IN’ operator

Posted in ArcGIS, ESRI, GIS by viswaug on January 20, 2009

I was working with the Filter Encoding (FE) 1.1 standard recently and was surprised to find that it did not have an equivalent to the ‘IN’ operator. The FE 1.1 standard currently supports the following comparison operator.

  •  
    • PropertyIsEqualTo
    • PropertyIsNotEqualTo
    • PropertyIsLessThan
    • PropertyIsGreaterThan
    • PropertyIsLessThanOrEqualTo
    • PropertyIsGreaterThanOrEqualTo
    • PropertyIsLike
    • PropertyIsBetween
    • PropertyIsNull

That was a big bummer for me since when I tried to use multiple “PropertyIsEqualTo” comparison operator coupled with the logical “OR” operator the performance was VERY bad. I am using ArcGIS server 9.3 like always.

The OGC support in ArcGIS Server is really great in case your are wondering. ArcGIS Server 9.3 supports the Filter Encoding 1.1, Styled Layer Descriptor 1.0 and other OGC standards as listed here http://www.esri.com/software/standards/standards_tables.html. Support for Styled Layer Descriptor 1.1 is not there yet but may happen in the future versions.

The FE 1.1 standard does include support for the ‘GmlObjectId’ object identifiers way of querying data. This can serve as the ‘IN’ operator if you are querying by the object identifiers. Unfortunately, I wasn’t. The Filter Encoding does include support for ‘Functions’ where custom filters can be supported by the GIS Server. And since, the ‘IN’ comparison operator is missing, it can be implemented as a custom ‘Function’ by GIS Servers to serve clients who desperately that functionality…

ArcObject, PostGIS and spatial operations performance

Posted in GIS by viswaug on December 19, 2008

 

I have caught myself cursing at ArcObjects numerous times for not being performant for some of the spatial operations I had done using it in the past. I have also heard the same about it from other developers I have come across in the past. But what I was always curious about was, how does ArcObjects compare in performance to the other open-source spatial libraries like PostGIS? Since I have always been swimming in the ESRI and .NET ocean, I have little or no knowledge on how the PostGIS and Java worlds performed. So, it was interesting to listen to John Coryat speak this thoughts on how using the PostGIS library (that sits on top of the PostgreSQL database) makes spatial operations up to 10 -100 times slower than if those operations where performed using the spatial operations built into Postgresql.

Both ArcObjects and PostGIS suffer from the same drawback that make them significantly slower, they both are spatial libraries that run outside of the process space of the database where the spatial data is stored. Please see comments from Paul Ramsey below explaining that PostGIS actually runs within the process space of the database and how recents changes have made it considerably faster. ArcObjects runs outside of the process space of the database where the spatial data is stored.And that takes a major toll on the performance. Most databases these days including MS SQL Server 2008 pack a powerful set of spatial operations built right now that can satisfy the requirements for some cases but definitely not all of them. The spatial operations that are built right into the databases should always be prefered over using external spatial libraries since they run magnitudes faster. But the problem is that spatial operations included in those databases are not comprehensive and they all don’t pack the same geometry operations either. Most of them do support the SQL functions outlined by the OGC like SQL Server 2008 but some don’t. The most notable memberwould be MySQL.

To overcome or to add more capability to the spatial operations in these databases, an external spatial library become a necessity. Expecting the database vendors to develop spatial operations and to have to them keep up with the developments in the industry would not be an attractive option. This puts us, the spatial developers, in a catch-22 situation wherein we need to give up on performance to gain more powerful spatial capabilities. So, we may not be able to FIX the performance issues with spatial operations but just patch it as needed.

Even though John Coryat talks about his experience with PostGIS and PostgreSQL performance comparisions, that still doesn’t give a comparision between ArcObjects and PostGIS performance. It would be interesting to see some performance benchmarks between the two though.

Exposing ArcGIS Server Object Extensions as a Web Service – Will save you some licenses

Posted in ArcGIS, ESRI, GIS by viswaug on September 30, 2008

What is good about building your application’s features as a Server Object Extension? Here are some

  1. Server Objects Extensions run inside the ArcGIS Server Context and lets you avoid those calls that go across processes and sometimes across machines. This can deliver a significant boost to your application’s performance and improve its reliability and scalability.
  2. Provides a way to incur some expensive initialization logic just once when the Server Object spins up. Server Object Extensions live and die with the Server Objects.
  3. Some caching logic can also be implemented on the Server Object Extensions to eliminate avoidable loads on the ArcGIS Server. Caching here can go a long way in improving the performance of the application.
  4. Allows developers to expose coarse-grained functionality by writing straight ArcObjects code. Not all developers fully recognize when to use ‘new’ or ‘CreateObject’ to create ArcObjects classes.

After the Server Object Extension (SOE) has been written, you will want to expose the service to your web applications. If you are writing Web Services to expose the SOE functionalities to your clients, you will still need to use the ‘AGSServerConnection’ or the ‘GISServerConnection’ classes to access the SOE’s methods. This means that the web server machine where you web service is hosted will need an ESRI license. If you want to farm out your web server, you will need an ESRI license for every one of your web servers.

But there is a way around this, your SOEs can be exposed as SOAP web services directly. This means that your clients can access the SOE as web services on the ArcGIS Server machine directly. Or you can build proxy web services or REST services that in turn expose the service to the clients. In order to expose your SOE’s methods as web services, you will need to do two things

  1. Create a new WSDL file with the definitions for the methods on the SOE. The name of the WSDL file should be the same as the name with which the SOE was registered with ArcGIS Server referred to as the Extension Type. The file should be saved in the ‘C:\Program Files\ArcGIS\XmlSchema’ folder.
  2. Implement the ‘IRequestHandler2’ interface on the Server Object Extension.

Once the above two steps have been accomplished, ArcGIS Server will now publish your SOE’s method as a web service. When the clients request a WSDL from the MapServer, it will serve the updated WSDL with the SOE’s method definitions in it. And when the clients make a request to the SOE’s web service, ArcGIS Server will route the request to the ‘HandleStringRequest’ method on the ‘IRequestHandler2’ interface implemented by the SOE.

The implementation of the ‘HandleStringRequest’ method on the SOE should parse through the SOAP request sent into it and retrieve the input parameters. The input parameters can then be used to execute the request and return the SOAP formatted response. This could be a major pain and I always had a major hang-up with writing code to parse SOAP request messages and form SOAP responses. But I just recently came to know that ESRI has recently exposed some internal classes that did exactly that. This wasn’t available in the 9.2 version and I don’t believe in the 9.3 betas. The ESRI class that would help in doing this is the ‘MessageClass’ class.

Yes, it is a little more work. But once you get the plumbing right once, it is easily repeatable. I believe that the extra effort on the developers part is well worth it in terms of $$$ saved in licensing.

Inserting spatial data in SQL Server 2008

Posted in .NET, C#, GIS by viswaug on September 29, 2008

I have been working on inserting data from FeatureClasses into SQL Server 2008 tables. There are a lot of examples online showing how spatial data can be inserted into SQL Server 2008 tables. Here is one from Hannes.

I took from cue from there and proceeded to do what every good programmer should do…parameterize the INSERT query. Here is a simplified version of the scenario I was trying to tackle…

sqlCommand.CommandText = INSERT INTO foo (id, geom) VALUES(1,@g);

sqlCommand.InsertParameter(“@g”, ‘POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))’);

The above INSERT command worked fine. But I also wanted to insert the geometry or geography with the WKID value from the FeatureClass. So I changed the above INSERT statement to use the geometry’s static method and provided the WKID as below

sqlCommand.InsertParameter(“@g”, ‘geometry::STGeomFromText(POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0)), 0)’);

But that didn’t go quite as expected. The INSERT statement was throwing an error saying that it did not recognize ‘geometry::STGeomFromText’ and was expecting ‘POINT, POLYGON, LINESTRING…’ etc.

System.FormatException: 24114: The label geometry::STGeomFrom in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.

This led me to use the Udt SqlParamter type to get around the problem like below. This way I could use the SqlGeometry type in the ‘Microsoft.SqlServer.Types’ namespace and set the SRID directly on its ‘STSRID’ property. The ‘SqlGeometry’ and ‘SqlGeography’ types can be found in the Feature Pack for SQL Server 2008 that can be found here. The SqlGeometry reference can then be used as the value for the SqlParameter.

SqlParameter parameter = new SqlParameter( parameterName, value );

parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = parameterName;
parameter.SourceColumn = sourceColumn;
parameter.SqlDbType = SqlDbType.Udt;
parameter.UdtTypeName = “GEOMETRY”;
parameter.SourceVersion = DataRowVersion.Current;

command.Parameters.Add( parameter );

The ‘UdtTypeName’ should be set to ‘GEOGRAPHY’ for inserting geography fields.

And it worked like a charm.

Update: Heard from Morten saying that using the SqlGeography and SqlGeometry types are almost twice as fast as using WKB (Well Known Binary). Using WKB is by itself faster than using WKT (Well Known Text).

Tagged with:

Can Yahoo Pipes or Microsoft Popfly replace ArcGIS Model Builder

Posted in ArcGIS, ESRI, GIS by viswaug on September 15, 2008

Yahoo Pipes and Microsoft Popfly are powerful online tools that allow us to aggregate, mashup and process data from various sources on the Internet to produce the desired output. These tools source their data from various URLs(resources) that can be added into the pipe (or model) and they can be manipulated using “Operators” that Yahoo Pipes make available.

Capture2 Capture3

GIS data can be fetched from ArcGIS Server through its RESTful endpoints in the format desired like JSON, KML etc. The “Operators” really doesn’t do too much for us here but they can be used as desired for limited purposes. The “Sub-element” operator especially can come in quite handy to get just at the desired part of the source data.

The operators that will interest the GIS folks are the geo-processing tasks that can be again accessed at RESTful URL endpoints in ArcGIS Server. The “UrlBuilder” shown below will come in real handy to direct from the source into the geo-processing task.

Capture5

The only thing that is missing right now is that I haven’t figured out how I can get a point geometry from a geo-coding task to be passed as the value for a query parameter for another geo-processing task.

But taking this line of thinking further, using Yahoo Pipes or Microsoft Popfly instead of ArcGIS model builder may not be that far fetched. I am not saying that it is going to happen, it does have a long way to go, but it could be a poor-man’s model builder.

Tagged with: , ,

KML support in ArcGIS Server 9.3

Posted in ArcGIS, ESRI, GIS by viswaug on September 14, 2008

I was looking into the KML support available in ArcGIS Sever 9.3 and realized that is only available for the results of queries on the MapServer, geoprocessing results and geocoding results. The “identify” results of the MapServer and the results of the geometry services like Project, Simplify, Buffer etc do not support output in KML format.

Tagged with: , ,

Quick note on KML support in the ArcGIS Server 9.3 REST API

Posted in ArcGIS, ESRI, GIS, Uncategorized by viswaug on March 23, 2008

ArcGIS Server 9.3 supports multiple output formats in its REST API

  • HTML
  • JSON
  • KMZ
  • image
  • help
  • lyr, nmf(ArcGIS Explorer document), jsapi(Javascript API), gmaps(Goolge Maps), ve(VirtualEarth)

Good news on the KML support though. The output is actually an KMZ output with includes all symbology for the features from the Map Service. The symbols are outputted as images and zipped to a KMX along with the KML file. This is great news because it will help reduce a whole lot of redundant work for developers. Obviously the other formats like JSON that do not support symbology do not include the symbology in them.

Should you have multiple maps (or dataframes) in your MapServer MXD for ArcGIS Server

Posted in ArcGIS, ESRI, GIS, Uncategorized by viswaug on March 22, 2008

At the ESRI Dev Summit 2008, I managed to get some insights into questions that have been bouncing around in my head from the ESRI folks building ArcGIS server. One such design question was whether it was reasonable to create MXDs with multiple maps in them that can be accessed as different map resources or just create different map services for multiple maps. By creating a map service with multiple data frames in them you can serve both maps with just a single instance of a SOC. When creating different map services for different maps you end up with different SOCs for each map service.

One scenario where you might ask yourself this question is when deciding whether the overview map should be in a data frame on the same map service or have a different map service for the overview map. Obviously, the overview map only has some of the layers from the map itself.

The REST API in ArcGIS Server 9.3 will only support the current data frame in the MXD.

Now to the answers, it is not a bad idea to have multiple data frames in a single MXD and consume them as separate resources as long as the multiple data frames (or maps) in the MXD do not get used simultaneously all the time. When used simultaneously, a single SOC process is going to process the requests for both the maps and thus the concurrent requests are going to slow down the service. If the maps are going to be used concurrently, then it is better to create different map services for each of the maps, this way different process can process the requests and can return faster. That being said, for most cases it is better to create separate map services for each map.

Little known bottlenecks for ArcGIS Server Map cache generation performance

Posted in ArcGIS, ESRI, GIS, Uncategorized by viswaug on March 22, 2008

I had a chance to ask some of the ESRI developers the reason why the time required for our map cache generation didn’t reduce by very much when we started running SOCs on multiple machines (more powerful machines with Dual Quad core processors) with increased SOC instances. The bottleneck in these cases happen to be the following

  • ArcSDE
  • Disk speed (RPM) of the cache destination

Ok, now for the reasons as to why they are bottlenecks and some suggestions on how they can be worked around. The map service for which we were generating caches for, had almost all of its layers coming in from a single ArcSDE instance. When a large number of SOC instances (or processes) start to generate the map cache, all of them are accessing the same ArcSDE instance concurrently to generate the map cache. This multiple simultaneous access of the ArcSDE could slow down access to the map data and thus slow down map cache generation. One way this bottleneck could be worked around is by copying the ArcSDE data to multiple file geodatabases on different machines and referencing different layers in the map service from different file geodatabases. This way access to the map data is not throttled at just a single machine and is split amongst different machines.

All the SOC instances are also outputting their cache files to the same output destination. This means that this could also be a bottleneck if the disk speed of the output destination is not fast enough. One way that I can think of to work around this issue is by running different map cache generation processes for different scale levels and have their outputs going to different machines. Once done, the caches for the different scale levels can be copied to a single destination(ESRI uses SecureCopy to copy map caches between disks for their ArcGIS Online services). But, I don’t believe that this technique would save any time at all and would be too much trouble for not enough returns. So, just spend some money on a really fast hard disk.

Larger tile sizes for map caches take lesser disk space and generating them take lesser time. ArcGIS Online uses a 512X512 tile size whereas VirtualEarth and Google Maps use a 256X256 tile size.