<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>Trip,</p>
<p>Sounds interesting. <br>
</p>
<p>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.
<br>
</p>
<p>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... )<br>
</p>
<p>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.</p>
<p>Another advantage of MySQL is the deep integration with Wikipedia
/ Wikimedia / Wikidata via php. <br>
</p>
<p>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 "<i>trees and hierarchies</i>" (see
for example: <i>Joe Celko's Trees and Hierarchies in SQL for
Smarties</i>). <br>
</p>
<p>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.<br>
</p>
<p>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. ) <br>
</p>
<p>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).</p>
<p>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.<br>
</p>
<p>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<br>
</p>
<p>Rick<br>
</p>
<br>
<div class="moz-cite-prefix">On 8/7/2017 5:06 PM, Tripp Corbin
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:01dc01d30fc0$fcda2a90$f68e7fb0$@egisassociates.com">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]-->
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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. <o:p></o:p></p>
<p class="MsoNormal"><img style="width:14.2395in;height:4.427in"
id="Picture_x0020_2"
src="cid:part1.C381A174.F449359F@clbcm.com"
alt="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"
class="" height="425" width="1367"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><i><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:black">Tripp Corbin, MCP, CFM,
GISP</span></i></b><b><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:black"> </span></b><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:black">| Chief Executive Officer</span><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:#1F497D"><br>
</span><b><span
style="font-size:18.0pt;font-family:"Times New
Roman",serif;color:#1F4E79"><a
href="http://www.egisassociates.com/"
moz-do-not-send="true"><span style="color:#1F4E79">eGIS
Associates, Inc.</span></a></span></b><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:black"><a
href="mailto:tcorbin@egisassociates.com"
moz-do-not-send="true"><span style="color:blue">tcorbin@egisassociates.com</span></a>
| <a href="www.egisassociates.com" moz-do-not-send="true"><span
style="color:blue">www.egisassociates.com</span></a> </span><span
style="color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:black">678-710-9710 ext 0021 |
678-672-8970 Direct Dial<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:black">Esri Certified Desktop
Professional | Esri Certified Enterprise System Design
Associate<o:p></o:p></span></p>
<table class="MsoNormalTable" cellspacing="0" cellpadding="0"
border="0">
<tbody>
<tr>
<td style="padding:3.75pt 3.75pt 3.75pt 3.75pt">
<p class="MsoNormal"><o:p> </o:p></p>
</td>
<td style="padding:3.75pt 3.75pt 3.75pt 3.75pt">
<p class="MsoNormal"><o:p> </o:p></p>
</td>
<td style="padding:3.75pt 3.75pt 3.75pt 3.75pt">
<p class="MsoNormal"><o:p> </o:p></p>
</td>
<td style="padding:3.75pt 3.75pt 3.75pt 3.75pt">
<p class="MsoNormal"><o:p> </o:p></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:black">Order my book <a
href="https://www.packtpub.com/application-development/learning-arcgis-pro"
moz-do-not-send="true"><span style="color:blue">Learning
ArcGIS Pro</span></a> now!<o:p></o:p></span></p>
<p class="MsoNormal"><a
href="https://www.packtpub.com/application-development/learning-arcgis-pro"
moz-do-not-send="true"><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:blue;text-decoration:none"><img
style="width:1.677in;height:2.0625in"
id="Picture_x0020_1"
src="cid:part6.58E1CFFA.377D8EA3@clbcm.com"
alt="B04578_MockupCover_Normal_" class="" height="198"
width="161" border="0"></span></a><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
SHRUG-L mailing list
<a class="moz-txt-link-abbreviated" href="mailto:SHRUG-L@lists.dep.state.fl.us">SHRUG-L@lists.dep.state.fl.us</a>
<a class="moz-txt-link-freetext" href="http://lists.dep.state.fl.us/mailman/listinfo/shrug-l">http://lists.dep.state.fl.us/mailman/listinfo/shrug-l</a>
</pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Richard J. Labs, CFA, CPA
CL&B Capital Management, LLC
Phone: 315-637-0915
E-mail (preferred for efficiency): <a class="moz-txt-link-abbreviated" href="mailto:rick@clbcm.com">rick@clbcm.com</a>
Mailing address: 408B Holiday Harbour, Canandaigua, NY 14424</pre>
</body>
</html>