POST
|
Tom, if you have remaining errors please open a support call. My guess is the geometry type suffix is an artifact of the reader not knowing the input geometry type, workspace generation will automatically create one (geometry named) feature type per input in those cases.
... View more
4 hours ago
|
1
|
0
|
12
|
IDEA
|
Related, an old sample: https://pm.maps.arcgis.com/home/item.html?id=9398bd2232cb4c8490b0b05015364d28
... View more
3 weeks ago
|
0
|
0
|
102
|
POST
|
For followers: Product team for Data Interop have recommended a test embedding the service account owner credentials into the SQL Server reader.
... View more
02-23-2024
01:07 PM
|
0
|
1
|
228
|
BLOG
|
Hello Annarita, since you mention a "workbench" I'll not wait for Jill to talk about replication and collaboration scenarios. You can do bi-directional synchronization between ArcGIS Online and enterprise geodatabase with Data Interoperability, but you will need to decide which system is parent and child when the same feature is edited in both systems. Maybe one system is where features are created and deleted but features can be edited in either, you make the rules. If you want to prefer one system's edits then run a workspace that commits its' edits to the child first, then a second workspace to fetch edits from the child. If you do not have a shared key field then you can only generate inserts and deletes, not updates. I'm thinking here of using the ChangeDetector transformer to generate the deltas.
... View more
02-20-2024
07:33 AM
|
0
|
0
|
580
|
POST
|
Hello Jeremy, apologies for the delay. Quick Export doesn't know about definition queries. In the attached is a Pro 3.2 toolbox with a spatial ETL tool in it that exports your query features to mbtiles, zoom levels 4-8.
... View more
02-16-2024
10:53 AM
|
0
|
0
|
246
|
BLOG
|
@ShareUser I suppose simple datasets in object stores could be used that way, but on the vector data front I'm personally interested in how AI could be used to generate SQL that gives you the view of GeoParquet that you want to consume, in a smart enough client.
... View more
02-14-2024
06:29 AM
|
0
|
0
|
385
|
BLOG
|
@ShareUser Now that is exactly the right question! GeoParquet certainly has some very attractive properties, such as a rich data type set (including arrays and structs so you can have an implied data model in one table), queryable metadata, support in the S3 API across the major object stores etc. This makes it a candidate to replace the venerable shapefile, if the user community chooses.
... View more
02-13-2024
11:18 AM
|
0
|
0
|
442
|
BLOG
|
@ShareUser A feature service with behaviors like relationship classes is easily digestible in ArcGIS Pro for mapping and analysis, a remote hive of GeoParquet files (much) less so, at least for a Pro user.
... View more
02-13-2024
11:07 AM
|
0
|
0
|
453
|
POST
|
Hi Matt There are two properties in play, feature operation and table handling. If you are always replacing the table data entirely then insert feature operation and drop and create table handling is simple. This will also create the table if it doesn't exist. If the table has relationship classes though, dropping the table will also drop the relates (which Data Interoperability cannot recreate), in which case you need to insert features with a truncate existing table handling. Most elegant is to also read the existing state of the target table, use a ChangeDetector transformer to figure out the delta, then use fme_db_operation feature operation to do insert, update and delete operations as required. Be aware though that ChangeDetector is brutally strict, things like date precision matter if you include datetimes in the compare.
... View more
02-13-2024
08:21 AM
|
1
|
0
|
231
|
BLOG
|
In an earlier post I got my feet wet using DuckDB in a hosted notebook in ArcGIS Online. That post highlighted reading GeoParquet files in an object store to maintain a feature service. That's a powerful workflow but it is much more likely your daily work involves wrangling common file formats which take up way too much time to ingest - like CSV, Excel and JSON. This post is about making your life easier by showing how DuckDB enables SQL for any file format and also supports simple conversion to Esri formats. If you have basic SQL and Python skills - as in you can read this sample and the web help - you will be fine. Here is some live action, Bloomington Indiana open 311 incidents migrated to a feature class in seconds. DuckDB in a Notebook The notebook (in the blog download) reads a CSV file at a public URL and writes a feature class into the project default geodatabase. Notebooks are a good place to start compared to a script tool because you can work in a cell interactively as you figure out your SQL. Once things are working you might copy the code to a script tool for easy automation on a schedule. But first you need to install DuckDB! The usual route to adding a Python package to your environment is to clone the arcgispro-py3 environment then add a package in the Package Manager UI backstage in Pro. You would look for a package named python-duckdb. This errored for me (it happens) so I did a manual install. I cloned my default environment to one I named arcgispro-py3-quack and installed DuckDB by opening the Python Command Prompt from the ArcGIS program group then running this command ('username' will be yours): conda install -c conda-forge python-duckdb=0.9.2 -p "C:\Users\username\AppData\Local\ESRI\conda\envs\arcgispro-py3-quack" --yes Make sure to get the latest DuckDB distribution, at writing it is 0.9.2. Let's walk through each notebook cell. The first one is pretty simple, the imports and some calls to duckdb to set up the environment. # Imports, environment
import arcpy
import duckdb
from arcgis.features import GeoAccessor, GeoSeriesAccessor
import os
import requests
duckdb.sql("install spatial;")
duckdb.sql("load spatial;")
duckdb.sql("install httpfs;")
duckdb.sql("load httpfs;")
arcpy.env.overwriteOutput = True The second cell makes a pandas dataframe by reading the CSV data while automatically inferring data types - such a big help. You'll notice I code defensively for the situation the server I'm hitting doesn't support HTTP range request access like DuckDB wants - I download the data - which in this case was necessary. url = r'https://data.bloomington.in.gov/resource/aw6y-t4ix.csv'
filename = os.path.basename(url)
base, extension = os.path.splitext(filename)
try:
sql = "create or replace view bloomington311_view as select * from read_csv_auto('{}');".format(url)
duckdb.sql(sql)
except:
response = requests.get(url)
with open(filename, 'w', encoding="utf-8") as f:
f.write(response.text)
f.close()
sql = "create or replace view bloomington311_view as select * from read_csv_auto('{}');".format(filename)
duckdb.sql(sql)
# Build the query you want in SQL to rename, cast or otherwise process the data.
# This dataset has a column in WKT already, if your data does not then you'll need
# to make one using the ST_ functions in DuckDB.
sql = """select service_request_id, requested_datetime, updated_datetime, closed_date, status_description, source,
service_name, description, agency_responsible, address, city, state, try_cast (zip as varchar(10)) as zip, sladays, request_complete_days, sla_diff_days,
geocoded_column as SHAPE from bloomington311_view where SHAPE is not null;"""
df = duckdb.sql(sql).df() Note the point about using or creating WKT values for your geometry. The spatial extension for DuckDB has a rich set of spatial functions. Now convert the dataframe to a feature class. You have other conversion options. Note that the arcgis Python API has no idea where the dataframe came from - DuckDB will do fine! - so I spatially enable the dataframe and bounce the data through a FeatureSet to get it into a geodatabase feature class. No wrangling of attribute field properties, they just work (see the SQL above, the classic situation of casting ZIP codes to text came up). The spatial reference got lost somewhere but I just reapplied it to the output. I think I found a bug in the expression df.spatial.to_featureclass(), I'll look into it. # Write the feature class
df.spatial.set_geometry("SHAPE",sr=4326,inplace=True)
aprx = arcpy.mp.ArcGISProject("CURRENT")
gdb = aprx.defaultGeodatabase
out_fc = arcpy.ValidateTableName(base,gdb)
location = os.path.join(gdb,out_fc)
#df.spatial.to_featureclass(location=location,overwrite=True,sanitize_columns=False)
fs = df.spatial.to_featureset()
fs.save(gdb,out_fc)
sr = arcpy.SpatialReference(4326)
arcpy.management.DefineProjection(in_dataset=location,coor_system=sr) Lastly, announcement! print("Created feature class {}".format(location)) That's it, fast, simple, smart migration of data with some help from DuckDB! The notebook is in the blog download, let us know how you get on.
... View more
01-23-2024
01:34 PM
|
7
|
0
|
701
|
BLOG
|
Every now and then a compelling workflow is enabled by new ideas, and data distribution by cloud native formats is my example today. Take this (very plain) map for example: Places Of Interest in London What you are looking at is a hosted group layer in ArcGIS Online with 364503 place points in an arbitrary extent (the bounds of all city boroughs) in London, England, with related tables; 364503 address details, 364503 common names, 23130 business brand names, 281408 website URLs, 487799 source attributions, 259100 social media link URLs, and 329497 phone numbers. If we explore places near the famous Harrod's premises in Kensington & Chelsea boroughs we can see dozens of business brands: Godiva Chocolates This being England, you might be interested in tea rooms about the city. Using place category and related tables we can see not just their locations but their address, website, social media links and phone numbers: Tea Rooms This place data is one of the themes from Overture Maps Foundation and is made available under this license. If you surf the Overture website, you'll see it is a collaboration of Amazon, Meta, Microsoft and TomTom as steering members, Esri as a general member, and many other contributor members and is envisaged as a resource for "developers who build map services or use geospatial data". I'm democratizing it a bit more here, giving you a pattern for consuming the data as hosted feature layers in your ArcGIS Online or ArcGIS Enterprise portals. Let's dig into the details of how to migrate the data to feature services. The data is made available at Amazon S3 and Azure object stores as Parquet files, with anonymous access. I'll let you read up on the format details elsewhere but Parquet is definitely one star of the cloud native show because it is optimized for querying by attribute column, and in the case of GeoParquet, this includes a spatial column (technically multiple spatial columns if you want to push your luck). As GeoParquet is an emerging format it still has some things that are TODO, like a spatial index (which would let you query by spatial operators), but Overture very thoughtfully include a bounding box property which is simple to query by X and Y. The technology that is the second star of the cloud native show is DuckDB. DuckDB enables SQL query of local or remote files like CSV, JSON and of course Parquet (and many more) as if they are local databases. Remote file query is especially powerful if the host portal supports the Amazon S3 REST API and a client that talks this can use HTTP to send SQL queries, which DuckDB can. Especially powerful is DuckDB's ability to unpack complex data types (arrays and structs) into a rich data model like I'm doing here (base features and 1:M related tables), and not just flat tables. Only the query result is returned, not the remote file. The third star of the cloud native show is ArcGIS Online hosted notebooks, which can be used to orchestrate DuckDB transactions and integrate the results into new or refreshed hosted feature layers in Online or Enterprise. The superpower of this combination of Parquet, DuckDB and Python is that global scale data can be queried for a subset of interest in a desired schema using industry standard SQL, plus automated . This forever deprecates the legacy workflow of downloading superset data files to retrieve the part you want. At writing, the places data resolves to 6 files totaling 5.54GB, not something you want to haul over the wire before you start processing! If you think about it, any file format you have to zip to move around and unzip to query (shapefile, file geodatabase) generates some friction, Parquet avoids this. The Notebook I used to import the places data is in the blog download. Notebooks aren't easy to share graphically but I'll make a few points. Firstly, ArcGIS notebooks don't include the DuckDB Python API, so it needs to be installed from PyPi, here is my cell that does the import and also loads the spatial and httpfs extensions needed for DuckDB in this workflow. import arcgis
import arcpy
from datetime import datetime
import os
import zipfile
def getNow():
return str(datetime.utcnow().replace(microsecond=0))
arcpy.env.overwriteOutput = True
sR = arcpy.SpatialReference(4326)
gis = arcgis.gis.GIS("https://www.arcgis.com", gisUser, gisPass)
!pip install duckdb
import duckdb
duckdb.sql("install spatial;")
duckdb.sql("load spatial;")
duckdb.sql("install httpfs;")
duckdb.sql("load httpfs;")
duckdb.sql("set s3_region='us-west-2';")
print('Processing starting at {}'.format(getNow())) I did not model the full Overture places schema, only the elements that looked populated in my area of interest. If you browse the places schema note the YAML tab. Don't be surprised in 2025 if you see YAML powering AI to let you have conversations with your data 😉 Each extract from the S3 hive follows the same pattern. Unfortunately a bug in DuckDB prevented me from using a more efficient approach (copying directly from memory to file geodatabase) so I fell back to ArcPy. Here is the cell that extracts address data - note the UNNEST operator that unpacks a struct. print('Extracting Addresses starting at {}'.format(getNow()))
sql = """with address as (select id, unnest(addresses, recursive := true) from places_view where addresses is not null
and {}) select id, freeform, locality, postcode, region, country from address;""".format(whereExp)
relation = duckdb.sql(sql)
addresses = arcpy.management.CreateTable(out_path=r"/arcgis/Places.gdb",out_name="Addresses").getOutput(0)
arcpy.management.AddField(in_table=addresses,field_name="id",field_type="TEXT",field_length=40,field_is_nullable="NULLABLE",field_is_required="REQUIRED")
arcpy.management.AddField(in_table=addresses,field_name="freeform",field_type="TEXT",field_length=1000,field_is_nullable="NULLABLE",field_is_required="NON_REQUIRED")
arcpy.management.AddField(in_table=addresses,field_name="locality",field_type="TEXT",field_length=100,field_is_nullable="NULLABLE",field_is_required="NON_REQUIRED")
arcpy.management.AddField(in_table=addresses,field_name="postcode",field_type="TEXT",field_length=1000,field_is_nullable="NULLABLE",field_is_required="NON_REQUIRED")
arcpy.management.AddField(in_table=addresses,field_name="region",field_type="TEXT",field_length=100,field_is_nullable="NULLABLE",field_is_required="NON_REQUIRED")
arcpy.management.AddField(in_table=addresses,field_name="country",field_type="TEXT",field_length=2,field_is_nullable="NULLABLE",field_is_required="NON_REQUIRED")
print('Addresses table created at {}'.format(getNow()))
with arcpy.da.InsertCursor(addresses,["id","freeform","locality","postcode","region","country"]) as iCursor:
row = relation.fetchone()
i = 1
if row:
while row:
if i % 10000 == 0:
print('Inserted {} rows at {}'.format(str(i),getNow()))
iCursor.insertRow(row)
i+=1
row = relation.fetchone()
del iCursor
print('Addresses table populated at {}'.format(getNow())) You'll see I set specific field widths for text fields. Common web formats (CSV, JSON, Parquet) don't let you tune data types like this but I like to do so. However if you're sharp eyed you'll see the postcode field has a crazy width of 1000. The pattern I settled on to determine field widths was to have a utility cell (in the download) in my notebook during construction which I used to explore the data. I found maximum field sizes that way. It turns out the crazy big postcode I found looks like a data error. Likely Data Error To go into production with this approach first figure out a query on extent or address fields (region, country) that works for you, then plug it into the notebook after sharing it to ArcGIS Online. You'll need to supply your own credentials of course, and change target gis if you're using Enterprise for the output feature service. To initialize the feature service, run the notebook manually down to the cell that zips up the working file geodatabase, download the zip file from the notebook and share it as an item and feature service, then plug in the item id. Then at each Overture release, change the theme URL to match the release and refresh your service. To give you a feel for performance, it takes a few minutes for the S3 hive to be queried for each layer but the results write at tens of thousands of features per second. It takes about 50 minutes for my test extent and data model, including overwrite of a target feature service. Naturally, if the data schema changes or to support other Overture themes, you'll need to author notebook changes or new notebooks. Do share! I'm sure you'll agree that this is a powerful new paradigm that will change the industry. I'm just following industry trends. It will be fun to see if Parquet is what comes after the shapefile for data sharing. The blog download has the notebook but not a file geodatabase (it's too big for the blog technology) but when you generate your own services don't forget the data license here. Have fun!
... View more
01-19-2024
11:27 AM
|
9
|
7
|
1124
|
IDEA
|
Hello Donald Data Interoperability extension for Pro can write TopoJSON. Regards
... View more
12-15-2023
06:19 AM
|
0
|
0
|
311
|
POST
|
Hello Ashley It seems you have struck an issue we haven't found a cause for: https://pro.arcgis.com/en/pro-app/latest/tool-reference/tool-errors-and-warnings/160001-170000/tool-errors-and-warnings-160226-160250-160236.htm If you open a support call the analyst can look at your workflow. Re. subscribing, if you subscribe to the whole Data Interoperability board you'll get everything, and thank you for asking.
... View more
12-06-2023
11:28 AM
|
0
|
0
|
281
|
Title | Kudos | Posted |
---|---|---|
1 | 4 hours ago | |
1 | 04-04-2023 10:54 AM | |
1 | 02-13-2024 08:21 AM | |
7 | 01-23-2024 01:34 PM | |
9 | 01-19-2024 11:27 AM |
Online Status |
Online
|
Date Last Visited |
4 hours ago
|