shrug-l: ArcGIS Pro - Joins and Relates

Tue Aug 8 17:35:01 EDT 2017

Trip,

Sounds interesting.

I'm not using ArcGIS, instead using QGIS (an open source, no cost 
alternative, but quite similar). I'm no stranger to joins and relates 
from Excel (and its underlying "power" data model which is pretty 
cool.)  I'm finding importing CSV and spreadsheets from many divergent 
sources are quite time consuming to easily bring into the QGIS 
environment directly, clean up the odd characters, non ANSI, datatype 
conversions, etc. MySQL thus far seems to work with the widest range of 
data sources for the Extract, Transform, Load (ETL) steps in my world.  
Excel is good from a "visual" prospective, but MySQL seems to be in the 
lead for larger (>100k rows) situations that won't all easily fit into 
memory.

MySQL plays very well with CSV, Excel, and QGIS. Another benefit is 
standard SQL statements work great on both MySQL and SQLite, another 
free database system that is very widely deployed cross platform (Linux, 
Android, IOS, Windows... )

Basically can use MySQL as  a "jack knife" to input and cleanse and 
harmonize data - then push it out to QGIS for mapping or MySQL/SQLite 
for end user applications.

Another advantage of MySQL is the deep integration with Wikipedia / 
Wikimedia / Wikidata via php.

In my mind the ultimate "relate" of the future is to link to RDF data 
such as Wikidata. SQL is quite similar to SPARQL which is very strong in 
linked data. Likewise have found that the SQL experts are no strangers 
to "/trees and hierarchies/" (see for example: /Joe Celko's Trees and 
Hierarchies in SQL for Smarties/).

Not sure what database structures lies at the heart of ArcGIS or QGIS 
but I'm finding that much of the data manipulation that I'm running into 
works best in a plain old SQL database (or SPARQL endpoint), and the GIS 
part is actually best "tacked on" late in the analysis game - using 
(predigested using SQL) industry standard shape files (with good o'l 
trusty .dbf attribute tables everyone can easily get their heads around 
and share.

Perhaps some hard core GIS folks here will disagree that spinning data 
relations inside a GIS system is easier/better? Probably comes down to 
how many geometry calculations are involved (distance from, clustering 
based on geo coordinates, meshing a geo polygon at various resolutions, 
dealing with lat/lon/altitude in 3-D geometries, etc.) A GIS oriented 
database might be the clear winner there (...unless we are talking 3D 
wire-frame "animation", driver-less car vision, where other 
"visualization" technologies oriented towards Neural Nets might be 
superior to a geo dbms. )

Still, for keeping it simple and "universal" I'd argue that SQL (and 
SPARQL) can't be beat for many, if not most "analytical" joins and 
relates. (Including using SQL Views that dynamically update).

Typically you have two types of SQL optimization: 1. for transaction 
recording and processing in near real time (lock that row or even just 
the field very briefly and update quickly, then replicate across 
redundant servers for very high read-only availability) or 2. spool off 
and "warehousing" the data so you can really grind on them for reporting 
(emphasis is on intense analytical processing and 
charting/mapping/graphing the output, etc.) GIS systems seem focused 
mostly in the second area. Yes, drawing the resulting map takes some 
resources but the "analytical" work prior to that rendering strikes me 
as pretty standard SQL.

One man's opinion...and happy, in fact delighted,  to hear other 
viewpoints on the best way to go from a data architecture (and/or 
performance) standpoint

Rick

On 8/7/2017 5:06 PM, Tripp Corbin wrote:
>
> So, I am working on a new ArcGIS Pro book which will contain some more 
> advanced topics than my first. One functionality I have been working 
> with is using Joins and Relates. ArcGIS Pro deals with these in an 
> interesting way.
>
> In ArcMap, you should use either a Join or a Relate depending on the 
> cardinality between the two tables. If you had a one to one or a many 
> to one cardinality, you should use a Join. If you had a one to many or 
> a many to many, you should use a Relate. If you Joined tables in 
> ArcMap when you should have used a relate, you ended up with one of 
> two things, either a table will extra fields that contained NULL 
> values or a table that joined the first record which was found and 
> ignored the other matching records. Neither was a positive result but 
> ArcMap would allow you to do it anyway.
>
> ArcGIS Pro handles it much differently. You can do a joint regardless 
> of the cardinality and you do not loose data or end up with NULL 
> values. ArcGIS Pro instead creates virtual records which are displayed 
> in the table. For example, a parcel layer and a table of all parcel 
> sales over the last twenty years. You join these two tables together 
> in ArcGIS Pro and select a single parcel which has sold 5 times over 
> the last 20 years. If you open the attribute table for the parcels, it 
> will show only one parcel is selected but will display 5 records for 
> that one parcel in the table view.
>
> mailbox:///C:/Users/rick/AppData/Roaming/Thunderbird/Profiles/ia5v1taz.default/Mail/mail.clbcm.com/Inbox.sbd/~Tallahassee.sbd/shrug-l?number=3349504&header=quotebody&part=1.1.2&filename=image001.png
>
> So unlike ArcMap, no data is lost and all results are displayed. 
> Further if you label by a field from the joined table it will display 
> the values from all matching records in the joined table.
>
> I really like how ArcGIS Pro handles joins when you have a 1 to many 
> or a many to many cardinality. It seems much cleaner than the way 
> ArcMap did.
>
> */Tripp Corbin, MCP, CFM, GISP/***| Chief Executive Officer
> *eGIS Associates, Inc. <http://www.egisassociates.com/>*
>
> tcorbin at egisassociates.com <mailto:tcorbin at egisassociates.com> | 
> www.egisassociates.com
>
> 678-710-9710 ext 0021 | 678-672-8970 Direct Dial
>
> Esri Certified Desktop Professional | Esri Certified Enterprise System 
> Design Associate
>
> 	
>
> 	
>
> 	
>
> Order my book Learning ArcGIS Pro 
> <https://www.packtpub.com/application-development/learning-arcgis-pro> 
> now!
>
> B04578_MockupCover_Normal_ 
> <https://www.packtpub.com/application-development/learning-arcgis-pro>
>
>
>
> _______________________________________________
> SHRUG-L mailing list
> SHRUG-L at lists.dep.state.fl.us
> http://lists.dep.state.fl.us/mailman/listinfo/shrug-l

-- 
Richard J. Labs, CFA, CPA
CL&B Capital Management, LLC
Phone: 315-637-0915
E-mail (preferred for efficiency): rick at clbcm.com
Mailing address: 408B Holiday Harbour, Canandaigua, NY 14424

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.dep.state.fl.us/pipermail/shrug-l/attachments/20170808/5c97beb1/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 48721 bytes
Desc: not available
URL: <http://lists.dep.state.fl.us/pipermail/shrug-l/attachments/20170808/5c97beb1/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.jpg
Type: image/jpeg
Size: 7704 bytes
Desc: not available
URL: <http://lists.dep.state.fl.us/pipermail/shrug-l/attachments/20170808/5c97beb1/attachment.jpg>