Latest Contributions by KimOllivier

And now at 10.8 the sqlite functions are broken again. Is it because Esri are now using Anaconda compilation? Sqlite3.dll has been compiled without RTrees! Great, that means we cannot use spatial data in python. The solution is to hack the installation and replace the sqlite3.dll in the DLLs folder with the latest version from sqlite.org. While we are hacking, why not replace the spatialite400x.dll with a full replacement for mod_spatialite by downloading from spatialite.org the latest extension suite. Since there is now a Libs/sqlite3 folder in the standard install, pop them in there and add it to the system path. ... View more

But which Sqlite spatial format? There are two systems already, the original spatialite which is handled with an extension to sqlite, like other databases, and the OGC geopackage format which is different to spatialite. ESRI influenced the geopackage format, and spatialite has bent to include the new metadata files. But there are still problems with the gpkg format. They forgot to include non-spatial tables in the format! So GDAL has added an extension that ESRI has not supported so far. Maybe they have recently, I haven't seen anything in What's New. I think spatialite is excellent, but there are a few things that obviously worries Esri developers. The type checking is lenient, like Excel, so they add a whole lot of constraints to the tables when creating them to avoid including text in numeric fields. There is no date type so dates have to be handled in functions. Dates default to an epoch of seconds since 1900, unlike anyone else and there are no converters outside sqlite. I store dates as strings so they can be transferred. FME is also very clunky when supporting spatialite. You have to use two writers and cannot use them together. It's all a mess. Spatialite has its own built-in spatial operators, used an R-Tree spatial index and is very very fast compared to a filegeodatabase. Because it supports SQL better there are some operations that are thousands of times faster. For example, add a field to a non-spatial table and populate it with geometry from another many to one related table, even from another database. Arcpy half-supports spatialite, you can use cursors and update tables but not geometry using the @shape operators. Copying from spatialite to another database is almost instantaneous compared to exporting to a file geodatabase. If you need to edit the data then simply use python and build an SQL query string by opening the database and extending the data to use spatial using the DLL already included in ArcGIS (because they use it under the hood for mobile apps). For interactive editing you will have to resort to QGIS so why not simply open it up in ArcPro?? It can't be any different to other geodatabases such as PostGIS or SQL Server, but without the overhead of a server. ... View more

Install the latest version of Pyscripter 3.6.2  https://sourceforge.net/projects/pyscripter/ Use the 64 bit version On the <Run> menu item there is an option to set the interpreter. You can switch back between 2.7 and 3.6 at any time. Its Marvellous. I can continue developing scripts in ArcMap and ArcGISPro using the same IDE and the same scripts. It will support virtual environments set up with ArcGISPro. I make all my 2.7 scripts 3.x compatible with a few simple changes to the syntax and adding __future__ to the top. Change all the print statements to print() functions (use the utility 2to3.exe in the scripts folder), a few other minor details and you are good to go! ... View more

You just use the ArcTool arcpy.management.AddSpatialIndex(). The help explains it will rebuild an existing index. Since the indexing is simpler you don't need to specify the spatial grid sizes. I have done this after loading data into a large featureclass and it makes a huge difference to drawing speed and processing. If processes appear to hang it is always worthwhile to interrupt the process, rebuild the indexes (including attribute indexes) and restart the process. Never be happy with any tool that takes longer than a cup of coffee. Find a better way. Note that you need to ensure that your features are small and contiguous relative to the full extent. If you have a large 'Mozilla' feature such as an enclosing coastline or large area punctuated with 'holes' representing forest you will defeat the spatial indexing. This is what the DICE tool is for, to split up unreasonably large polygons. The same applies to polylines. A single line representing a national highway included with short local roads will make all processes that use an index be very slow. Also do not be tempted to dissolve by a single attribute to 'normalize' the attribute table where one zone is represented by thousands of multi-part polygons. Running processing across a network is likely to be very slow. Process locally, which means: extract, process, replace. ... View more

You can do this in ArcTools by creating a custom tool and then add your custom tool to ModelBuilder. It is not clear if you want to avoid any Python programming, but that is the simplest way to do exactly as you asked. You create an empty template layer (for geometry type, fields and symbology). Then you create a new tool and set the dialog to input a featuredataset (it needs the template). When the tool opens it asks you to draw some graphics similiar to the graphic Draw tool. You can also set attributes if needed, there can be more than one feature. It is very flexible. When the tool executes it passes the featuredataset as a parameter. This can be turned into a featureclass and exported as an output for use in the ModelBuilder chain. Interacting with the map in a script was always much easier to do in Workstation/AML and later ArcView/Avenue. If you are still interested in this approach I could post an example script. Here is the help where I needed the user to sketch a proposed marine reserve:   Create MPA from Sketch or Features   Zoom to the area   Use the interactive sketch tool to draw polygons on the map You can use the graphic tools to draw circles, ellipses, rectangles or freehand polygons You can edit the graphic shapes, adjust vertices and in the graphic properties adjust to an exact coordinate   When you select [OK] the parts will be assembled and saved to a new featureclass with the default name or the one you specified.       ... View more

I have a lot of Python workflows in 'legacy 2.7' that will have to move to 3.6 and ArcGISPro or Server. My solution so far is to simply upgrade each 2.7 script to be cross compatible to 2/3 so it does not matter which interpreter is being used. There are only a few trivial syntax changes and a few other changes that can be handled with a test for the python version. I have not found any benefit to moving to 64 bit for the vector and database work that I do. Maybe for raster processing where more is done in memory, but even then, do some partitioning to reduce the requirement. ... View more

The latest 64 bit version of Pyscripter 3.6 does now handle conda environments. This saves you having to switch IDEs. It does interfere with Pyscripter32 even though they install in different places. But you can edit and run most scripts with the 64 bit version anyway. https://sourceforge.net/projects/pyscripter/ ... View more

Maybe that is why using spatialite on large datasets works so much better. ... View more

It is true that sqlite is still not supported for interactive editing as at Pro 2.3 and it is also not supported for editing in ArcMap. But don't let that put you off using sqlite and the spatial extensions combined with Esri tools. It is still very useful for lots of purposes. It can fill in holes in ArcTools and for some operations it is 100 times faster. The way I use it is to keep the spatial tables (ie featureclasses) and aspatial tables (ie tables) in sqlite (with the spatial extension DLL built into Arc*) and run python scripts with SQL commands. You can do any (non interactive) editing with that method! After a few seconds of processing I then copy the spatial tables back into a filegeodatabase if I need to use other tools that do not support sqlite. I can see the featureclasses and tables in ArcCatalog and they can usually be a read-only source of data. The great benefit to exporting data as a sqlite database is that I can send it to a user without a GIS installation and they can easily read it with an ODBC driver.It is a single file database excellent for exchange. There are no size limits. Some of my databases are 6GB. Because they have good indexing they are way faster than Access ever was.  Instead of an awful CSV file you get a proper database table with a full schema, metadata, and the flexibility of a relational database. Need the equivalent of Access?There are several open source equivalents available I purchased sqlitepro  http://www.sqliteexpert.com to test out my sql expressions. What sort of tools? Consider if you have a many to one relate between owners and parcels. You would like a point layer of owners 'geocoded' by using the parcel  centroid. The obvious solution is to add a spatial column to Owners and populate it with parcel centroids from a relate to the parcels. This typically takes 2 minutes for 5 million records. This is not easy with ArcTools. MakeQueryLayer is not available for filegeodatabases, MakeQueryTable  is slow, does not scale well enough to complete. The only workaround I have found is to create a new empty Owner featureclass, copy all the data across with a cursor, then create a dictionary of geometry objects from the parcels and update the Owner featureclass. This takes some advanced python skills so I may as well write a SQL expression in spatialite (sqlite + mod spatialite) sql = """UPDATE {0} SET shape = (SELECT shape FROM parcel.Parcel_Label B WHERE B.par_id = {0}.first_par_id) """.format(new_title)‍‍‍‍‍ time 0:00:17.682000 count 2149376 This was as expected because it was a port from ARC/INFO and AML which took a similar time. ... View more

Spreadsheets and even shapefiles should only be regarded as data sources. Immediately translate them into a file geodatabase and permanently join them to the spatial datasets. There are huge benefits in having all the data in a flat denormalised table for analysis. You may have to normalise data for efficient maintenance, but for analysis it has to be flat. You can then index the geometry field efficiently for spatial queries and the attributes can all be indexed as well. Index everything since there is no harm on a extracted database since you are not using it in a transactional database. Don't worry about repeated columns and other relational db caveats. You can still do subsets equivalent to where clauses and build on the because a GIS has persistence available similar to a view, called a layer, that does not need to copy the subset. You don't say how large your database is, but if you have spreadsheets (65,000 rows max?) and shapefiles (2GB max) then I would consider that you datasets are "small". Most single operations should complete in a few minutes so a complete workflow should be able to be run in a reasonable time. When testing out the workflow, get subsets to make it run even faster ... View more

Use sqlite instead. It is open source, 64bit, supports spatial operations, has unlimited size, supported by all vendors, including Esri! It is much faster that access or filegeodatabases, has full SQL support and is already built into ArcGIS. Sqlite is used in all the mobile apps because it is built into Android and Apple operating systems. There are ODBC drivers for all Databases, and it is built into Python. Have a look at the Toolbox or help and you will find it tucked away. ... View more

You are not clear if the 'database ' is spatially enabled. If so, then you can run sql queries that include spatial operators. But if it is a non-spatial database, then you will need to use a relate to the table that joins to a featureclass and you can then run Arctools for the spatial operations. Actually it is best if you just replicate the tables (or a subset) in the geodatabase for speed and repetition, because you will need to run it many times until you get the process correct. You might use ModelBuilder to create some workflows because inevitably there will be many steps. Or you can script the steps in python. Python is your friend here, because there are some efficiencies possible to hold tables in memory and index them using dictionaries before running the tools. Since the tools do not optimise the query (like sql) you will need to be smart to get efficient workflows. Indexes are important, tolerances, search limits, selection sets and the right tools for the process all take experience that will be more than a week to collect. The tools often do not work as efficiently as sql queries so do them in the database first if possible. ... View more

Can you have another go at formatting the code? It is unreadable. My strategy for the joinfield tool is Don't. Especially if it is a large table. You will have trouble with joins across databases, and if it is in the same database, use a database join. Instead I extract the data I need using a dictionary of tuples of the required fields using a SearchCursor dictionary comprehension. Then add some fields to the target, or a copy and populate using an UpdateCursor. This is always faster, reliable and works across databases. It does not need an index because a dictionary has a hash table underneath the hood. To allow for missing values use the get function with a default of None. ... View more