Edit files in a mounted Linux directory in Windows

Sometimes it is very useful to be able to edit files stored on a Linux machine in a Windows application. This can be a handy setup when you want to store your source code on Linux to be able to execute it against a Linux Python interpreter but you would like to edit it in a rich GUI application such as PyCharm or Eclipse. To achieve this, you can use an open source framework that mounts a Linux directory as a Windows drive from which you can add your files to a PyCharm project.

Another use case is when an application you need to use is available under Windows only, but copying the files from Windows to Linux upon every edit is tedious.

To mount a Linux directory in Windows:

  1. Install https://www.microsoft.com/en-US/download/details.aspx?id=40784 (install x64).

  2. Install https://github.com/dokan-dev/dokany/releases/tag/v0.7.4 (ignore a message saying do you want to download the VS runtime – this is because it installs libraries for x86 you will not need, just click Cancel).

  3. Download and run https://github.com/feo-cz/win-sshfs/releases/tag/1.5.12.8. It will be available in your tray.

  4. Mount a drive as described in the section Using Win-SSHFS to Mount Remote File Systems on Windows at https://www.digitalocean.com/community/tutorials/how-to-use-sshfs-to-mount-remote-file-systems-over-ssh.

You can optionally choose to mount a Linux directory on Windows start up. Extremely handy.

Advertisements

Warning: new GDB_GEOMATTR_DATA column in ArcGIS geodatabase 10.5

This post would be of interest to ArcGIS users who are upgrading enterprise geodatabases from ArcGIS 10.1-10.4 version to 10.5+ version. According to the Esri documentation and resources (link1, link2, link3):

Feature classes created in an ArcGIS 10.5 or 10.5.1 geodatabase using a 10.5 or 10.5.1 client use a new storage model for geometry attributes, which stores them in a new column (GDB_GEOMATTR_DATA). The purpose of this column is to handle complex geometries such as curves. Since a feature class can have only one shape column, the storage of circular geometries must be stored separately and then joined to the feature class when viewed in ArcGIS.

This means that if you create a new feature class in an enterprise geodatabase (either manually or by using a geoprocessing tool), three fields will be created: the OID field (OBJECTID), the geometry field (SHAPE), and this special GDB_GEOMATTR_DATA field. To be aware of this is very important because you will not be able to see this column when working in ArcGIS Desktop or when using arcpy.

The GDB_GEOMATTR_DATA field is not shown when accessing a feature class using arcpy.

[f.name for f in arcpy.ListFields('samplefc')]
[u'OBJECTID', u'SHAPE', u'SHAPE.STArea()', u'SHAPE.STLength()']

Querying the table using SQL, however, does show the field.

select * from dbo.samplefc
[OBJECTID],[SHAPE],[GDB_GEOMATTR_DATA]

If you are working with your enterprise geodatabase only using ArcGIS tools, you may not notice anything. However, if you have existing SQL scripts that work with the feature class schema, it is a good time to check that those scripts will not remove the GDB_GEOMATTR_DATA column from the feature class. This could happen if you are re-constructing the schema based on another table and have previously needed to keep the OBJECTID and the SHAPE columns. After moving to 10.5, you would also keep the GDB_GEOMATTR_DATA column.

Keep in mind that deleting the GDB_GEOMATTR_DATA column will make the feature class unusable in ArcGIS. Moreover, if this feature class stores any complex geometries such as curves, deleting the GDB_GEOMATTR_DATA column would result in data loss.

Trying to preview a feature class without the GDB_GEOMATTR_DATA column in ArcCatalog would show up the following error:

database.schema.SampleFC: Attribute column not found [42S22:[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘GDB_GEOMATTR_DATA’.] [database.schema.SampleFC]

Even though very unlikely to happen, trying to add a new field called exactly GDB_GEOMATTR_DATA to a valid feature class using ArcGIS tools would also result in an error:

ERROR 999999: Error executing function.
Underlying DBMS error [Underlying DBMS error [[Microsoft][SQL Server Native Client 11.0][SQL Server]Column names in each table must be unique. Column name ‘GDB_GEOMATTR_DATA’ in table ‘SDE.SAMPLE1’ is specified more than once.][database.schema.sample1.GDB_GEOMATTR_DATA]]
Failed to execute (AddField).

Obviously, trying to add the GDB_GEOMATTR_DATA using plain SQL would not  work either:

ALTER TABLE sde.samplefc
ADD GDB_GEOMATTR_DATA varchar(100)

Column names in each table must be unique. Column name ‘GDB_GEOMATTR_DATA’ in table ‘sde.samplefc’ is specified more than once.

Generating a database schema report using SchemaSpy

I recently wanted to generate a visual report over a database to see what tables, relationships, and other objects are present. I also wanted to see the schema of all the tables. The registrant Python package I’ve written does give me most of the things I want but it was not designed to fetch any relational structure such as primary/foreign keys and constraints.

I found an excellent free command line tool tool, SchemaSpy. It requires Java so it may be an extra thing to fix if you do not have it installed. The syntax for Microsoft SQL Server:

java.exe -jar .\schemaspy-6.0.0-rc2.jar -t mssql05 -dp .\sqljdbc42.jar -db non_spat -host localhost -port 1433 -u user -p password -o C:\Temp\sqlschema

You would need to download the Microsoft JDBC Driver for SQL Server if you work with MS SQL Server (I used the version 6).

SQL Server spatial functions for GIS users

If you have been using SQL Server for some time, you’ve probably heard of the spatial data support. This might be particularly interesting for anyone who is using any desktop GIS for data management and analysis. If you are an ArcGIS user and have enterprise geodatabases stored within SQL Server databases, you might have wondered whether it is possible to interact with the spatial data. This is useful when you don’t have a chance to use ArcMap to access the database due to some restrictions (permissions, network connections or software compatibility).

Well, you actually can do a whole lot with your geographic data just with SQL. It is important that you define the Shape field as of the Geometry/Geography data type. For most of the GIS work, you’d probably choose Geometry type which represents data in a Euclidean (flat) coordinate system. As soon as you have a geodatabase feature class which has the Shape field defined as of Geometry type, you can use native SQL Server tools to interact both with the feature class attributes and geometry.

Beginning with ArcGIS 10.1, feature classes created in geodatabases in SQL Server use the Microsoft Geometry type by default. To move your existing feature classes to the Geometry storage type, use the Migrate Storage geoprocessing tool or a Python script.

Alright, so after you have copied your file geodatabase feature class into a SQL Server geodatabase, you are ready to use native SQL to interact with the spatial data.

Let’s select all the features from the Parcels feature class.

SELECT * FROM dbo.PARCELS

Because we have a SHAPE column that of Geometry type, we get another tab in the results grid – Spatial results. There you can see your geometries visualized.

Microsoft SQL Server Management Studio

Let’s see what coordinate system our feature class was defined in.

DECLARE @srid INT = (SELECT TOP 1 shape.STSrid FROM dbo.PARCELS)
SELECT @srid AS SRID,
srtext AS Name FROM sde.SDE_spatial_references WHERE auth_srid = @srid

Here we use <GeometryColumnName>.STSrid to get the spatial reference id (SRID) of the coordinate system of the first feature. Because our geographic data is stored in a projected coordinate system (and Geometry type), we cannot get its name by using core SQL Server spatial references table, sys.spatial_reference_systems.

Here is why:

The coordinate systems in this table are for the geography type only as it contains information about the ellipsoid that is required to perform calculations. No such information is required to perform calculations for projected coordinate systems on the plane used by the geometry type, so you are free to use any reference system you like. For the calculations done by the geometry type, it is the same no matter what you use.

Let us explore next what kind of geometry is stored within a table. It is possible to store different types of geometry (such as polygon and polyline) within one table in SQL Server.

Let us see if it is true:

SELECT Id,GeomData AS Geometry,GeomData.STAsText() AS GeometryData
FROM [testgdb].[dbo].[GeneralizedData]

Microsoft SQL Server Management Studio

Yes indeed we store in one table features of different geometry and SQL Server has no problems with that. To be able to visualize this table in ArcMap though, you would need to use a query layer which is basically stand-alone table that is defined by a SQL query. ArcGIS can only handle having one type of geometry stored within each feature class which is why you will get a choice to pick what type of geometry do you want to look at.

New Query Layer After adding this layer into ArcMap, you will be able to see the polygons (provided you’ve chosen the polygons). The query layer is in read-only, so you cannot edit features in ArcMap. If you have a SQL Server table (non-registered with geodatabase) with multiple types of geometries stored, you will be able to switch easily between by adding multiple query layers into ArcMap defining what kind of geometry you want to work with.

Let us keep working with a geodatabase feature class which has only polygons. Let’s check if it’s true:

SELECT Shape.STGeometryType() AS GeometryType FROM dbo.PARCELS

Alright, so we know already what kind of coordinate system the data is stored in and we know that there are polygons. Let us get the perimeter (length) and the area of those polygons.

SELECT PARCEL_ID, SHAPE.STArea() AS Area,
SHAPE.STLength() AS Perimeter
FROM dbo.PARCELS 

Microsoft SQL Server Management StudioWe can also get the coordinates of each polygon within our feature class. Note that that the start point and the end point are identical – that is because each polygon is considered to be closed:

SELECT Shape.STAsText() AS GeometryType FROM dbo.PARCELS

This is what we will get:
POLYGON ((507348.9687482774 687848.062502546, 507445.156252367 687886.06251058145, 507444.18750036607 687888.56250258372, 507348.9687482774 687848.062502546))

There are similar functions such as .STAsBinary() which returns the Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation of a geometry instance and .AsGml() which returns the Geography Markup Language (GML) representation of a geometry instance.

We can also check the number of vertices per polygon:

SELECT PARCEL_id,
Shape.STAsText() AS GeometryDesc,
Shape.STNumPoints() AS NumVertices
FROM dbo.PARCELS
ORDER BY NumVertices DESC

Microsoft SQL Server Management StudioAlright, that was probably enough querying data. Let us check what kind of GIS analysis is available to us with native SQL. The easiest way to get started is probably to process the features of a feature class and then write the resultant geometries into a new table.

DROP TABLE [ParcelEnvelope]
CREATE TABLE [dbo].[ParcelEnvelope]([Id] [int] NOT NULL,
[PolyArea] int,[GeomData] [geometry] NOT NULL) ON [PRIMARY]

INSERT INTO ParcelEnvelope (Id,GeomData,PolyArea)
SELECT PARCEL_ID AS Id,
SHAPE.STEnvelope() AS GeomData,
SHAPE.STArea() AS PolyArea
FROM dbo.PARCELS
ORDER BY OBJECTID

This will create a new table where the envelopes of each parcel polygon will be written to.

Feature envelopeLet us do some buffers on road centerlines geodatabase feature class:

DROP TABLE [RoadBuffer]
CREATE TABLE [dbo].[RoadBuffer]([Id] [int] NOT NULL,
[GeomData] [geometry] NOT NULL) ON [PRIMARY]

INSERT INTO [RoadBuffer] (Id,GeomData)
SELECT OBJECTID AS Id,
SHAPE.STBuffer(50)
FROM dbo.Road_cl
ORDER BY OBJECTID

You can of course write newly generated features into a geodatabase feature class, not just a SQL Server database table. You need to create a new polygon feature class and then run the SQL below. This will create buffer zones for every line found in the Road_cl feature class.

DELETE FROM FC_ROADBUFFERS
INSERT INTO FC_ROADBUFFERS(OBJECTID,SHAPE)
SELECT OBJECTID AS OBJECTID,
SHAPE.STBuffer(50) AS SHAPE
FROM dbo.Road_cl
ORDER BY OBJECTID

Please refer to the Microsoft Geometry Data Type Method Reference to get a full list of available functions and more detailed description.

Try doing some other analysis such as finding what features intersect or overlap or how many points are located within a certain polygon. There is so much you can do! To learn more, get a book Beginning Spatial with SQL Server 2008 which has tons of examples and will also help you understand the spatial data structure basics. I have read this book and really liked it. I think it is a must read for anyone using spatial SQL.

I hope this short introduction into what you as a GIS user can do with SQL Server will help you take advantage of using the native SQL functions wherever using a desktop GIS is not an option.

Deploying Silverlight application with RIA services enabled

Deploying a Silverligtht application with WCF services and RIA services can be a time consuming process and may require extensive research on forums and MSDN. Here are just some of the tips I figured out recently.

To deploy a Silverlight application with RIA services on Windows 7/Windows 2008, follow the steps outlined below:

1. Run Visual Studio as Administrator: Right-click the Visual Studio shortcut and choose Run as Administrator. This is required for obtaining proper system permissions when publishing applications to the IIS inetpub folder. If you want to set up always running the Visual Studio 2010 as administrator in Windows 7, go to the Microsoft Visual Studio 2010 shortcut, right-click and choose Properties. Then switch to the Compatibility tab and check the Run this program as an administrator option in the Privilege level section.

2. Open the Silverlight solution: File > Open Project/Solution.

3. Right-click the ProjectName.Web and choose Publish. Choose as publish method the File System. Point to the folder to which the application will be published. Check the Delete all existing files prior to publish to make sure that all existing files within the folder will be deleted.

4. Open IIS Manager: To open IIS Manager from the Start menu – click Start, and then click All Programs. Click Administrative Tools, and then click Internet Information Services (IIS) Manager. To open IIS Manager from the Search box – click Start. In the Start Search box, type inetmgr and press ENTER. Go to the Default Web site. Right-click the folder where the application was published to and choose Convert to application. In the Add application dialog box, choose ASP.NET v4.0 application pool and not ASP.NET v4.0 Classic.

If for some reason, you are missing these pools even though you have .NET Framework 4 installed, you can fix this by registering ASP. Open your command prompt and run: cd C:\Windows\Microsoft.NET\Framework\v4.0.30319\. Then run: aspnet_regiis.exe -ir.

5. Right-click the application folder in the IIS manager and select Switch to content view.

Select .html or .aspx file and choose Browse in the Actions panel.

6. You will get an error message, because the RIA service was not created yet on-the-fly.

7. In order to make the application run as designed, you have to create a service in the Web browser first. Open an instance of Internet Explorer, and go to the URL:

http://machinename/appname/<Projectname>-Web-Services-<name of the service file>

Name of the service file can be obtained from the Visual Studio Solution Explorer: Go to <ProjectName>.Web and then expand Services folder to see the name of the created service.

Your URL may look like this: http://localhost/SL_Work/SlEventManager-Web-Services-EventManagerDomainService.svc. The URL may look different depending on the structure of the files in the Solution Explorer, so adopt this example. Going to this URL is crucial because you will not be able to view the application correctly without creating the service first.

Select .html or .aspx file in the IIS Manager and choose Browse in the Actions panel.

8. If you get an error saying related to the local user profile, check what authentication settings you have for the application. Opet IIS Manager and go to your application and then select Authentication in the Features view. You should have disabled: ASP.NET Impersonation, Windows Authentication and Basic Authentication. You should have enabled: Forms Authentication and Anonymous Authentication.

9. Now you can select .html or .aspx file in the IIS Manager and choose Browse in the Actions panel.

Hopefully it works!

WinXP Mode in Windows 7

OK, so now we have got some of the customers upgrading to ArcGIS 10. Some still use ArcGIS 9.3/9.3.1, though. Most of them do not have any particular need to upgrade at the moment. Others, perhaps, are not that familiar with the new functionality available in order to pursue the new version. Moreover, when moving from one platform to another or from one version of the software to another, providing a smooth transition of the workflows is always critical. These principles are applied in the same degree to the enterprise-wide operating system upgrade and to a tiny application, which might support a very specific highly-customized workflow within a small organization.

ESRI ArcGIS suite is a rather large platform regardless of the scale of use and shifting to another version can sometimes be cumbersome. New system requirements, deprecation of a certain technology, renewed interface design – all of these can take some time to get to grips with. In ArcGIS 10, we have got many updates in all of the family products, yet a large number of users need to support their specific ArcGIS 9.3 operations. Many of those users are willing to upgrade to the ArcGIS in order to take advantage of the new functionality, but they still must have ArcGIS 9.3 installed on their machines to be able to get their work done.

Recently, I have upgraded one of my machines to Windows 7 Professional 64 bit operating system. Personally, I like it very much – the graphic design is more appealing comparing to Windows XP, the customization options are fantastic, and the new functionality features are just great. A lot of people I know have upgraded their XPs to Windows 7, too. ESRI Training courses are designed for official support of Windows 7 as well. So it seems like we are going to have more people starting to use Windows 7 on their machines, both at home and in the office.

While browsing on the MSDN Web site, I have run into a Windows 7 feature called Windows XP Mode. As it turned out, Windows XP Mode is basically a new feature of Windows 7 (Professional, Enterprise, Ultimate), which provides additional application compatibility, i.e., it makes it possible to have a virtual machine with a preconfigured Windows XP Professional SP3 operating system where you can install your applications and then run them from the Windows 7 graphical user interface, as if they were actually installed in the Windows 7. Thus, the Windows XP Mode technology utilizes the Microsoft virtualization platform, Windows Virtual PC, to provide a virtual Windows XP environment for Windows 7.

If you are speculating over whether to give ArcGIS 10 a go, yet you still would like to have ArcGIS 9.3 at hand and have Windows 7 on the machine at your disposal (or you are planning to get one), you can consider activating Windows XP Mode in the Windows 7 and can start taking advantage of this technology. Personally, though, I have been using only VMWare virtualization products (mostly VMWare Server and VMWare Workstation, but some ThinApp, too) for the past few years. So, to set up a virtual machine with XP and ArcGIS installed in VMWare Workstation takes roughly an hour. However, this solution is not able to provide this “seamless” integration of the applications on the Win7 and WinXP. Moreover, Windows XP mode feature is already included in the Win7 license, so all you have to do is download and install a certain Windows 7 update. This very process can take an hour or so, which is still a very short period of time, considering the fact that you can continue to work on your machine – except when you have restart your computer, in order for the updates to come into action.

Personally, I have ArcGIS 10 installed on the Win7 (physical machine) and ArcGIS 9.3 installed in the WinXP (virtual machine). One can access the disk drives of your physical machine with Windows 7 as well, which provides users with a very smooth workflow indeed.

You can get started with Windows XP Mode in Windows 7 at the Microsoft Web page:

http://www.microsoft.com/windows/virtual-pc/download.aspx

Microsoft workshop

Recently I was at the Microsoft workshop.  Certain participants made really good presentations on some hot topics such as Silverlight 4 development. Some updates have been mentioned, such as that after installing Silverlight you can run Silverlight desktop applications offline without the need for an Internet connection, which is really good in the case of any security policy within an organization. Microsoft apparently do not want other organizations to distribute Silverlight distribution files with the custom application installation files due to security reasons – they want users to download all the files from their corporate Web site. Of course, it can be discussed with Microsoft and such a thing can be allowed – I think in some cases it is rather good to have those files at hand, because, in this case, a customer does not have to visit the Microsoft Web site and download the Silverlight files, yet the Silverlight plug-in will be installed automatically in the process of custom application installation.

We have been shown some parts of Microsoft Expression Studio. This is a really impressive thing. Together with Microsoft Expression Blend 4 and SketchFlow application a product manager is provided with very useful tools for application prototyping I have got more input about some tools for application design as well, which are available for trying at the Microsoft Web site Microsoft Windows Expression Blend 4 for Windows Phone Beta, which is a very neat tool for mobile application user interface and user interaction design tool, has been released and is free so far.

We have briefly gone through using SQL Azure solution for the cloud. So, the idea behind the use of databases in the cloud is that one can connect to a database which is hosted on one of the Microsoft servers from a local computer’s Microsoft SQL Server Management Studio. The information about using ESRI ArcGIS suite in the Amazon EC2 cloud might be relevant to this Microsoft product as well.

To conclude, the workshop was extremely fruitful, thanks go to Microsoft Technical Evangelists Danwei Tran (@danweitran) and Robert Folkensson. Thanks guys, well done!