<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Michael Flaxman on Medium]]></title>
        <description><![CDATA[Stories by Michael Flaxman on Medium]]></description>
        <link>https://medium.com/@mflaxman?source=rss-f82bd9e48bf9------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*173AU5GHyrGIWgTH.</url>
            <title>Stories by Michael Flaxman on Medium</title>
            <link>https://medium.com/@mflaxman?source=rss-f82bd9e48bf9------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 21:32:28 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mflaxman/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Importing  California Traffic Collisions Data Into OmniSci]]></title>
            <link>https://medium.com/@mflaxman/importing-california-traffic-collisions-data-into-omnisci-e61ff7f796b1?source=rss-f82bd9e48bf9------2</link>
            <guid isPermaLink="false">https://medium.com/p/e61ff7f796b1</guid>
            <category><![CDATA[hackathons]]></category>
            <category><![CDATA[traffic]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[gis]]></category>
            <dc:creator><![CDATA[Michael Flaxman]]></dc:creator>
            <pubDate>Sat, 03 Nov 2018 18:10:10 GMT</pubDate>
            <atom:updated>2018-11-03T18:10:10.423Z</atom:updated>
            <content:encoded><![CDATA[<h3>Importing Traffic Collisions Data Into OmniSci: Why and How</h3><p>There are a number of important public policy initiatives under way which can benefit from increased public access to — and understanding of — traffic accident data. For example, <a href="https://en.wikipedia.org/wiki/Vision_Zero">Vision 0</a>, is gaining traction in many US cities.</p><p>The first steps are relatively obvious — getting open traffic data online, or interactively available in focussed web apps. UC Berkeley has done this for California, making statewide traffic accident data widely available for online viewing and download.</p><p><a href="https://tims.berkeley.edu/">TIMS - Transportation Injury Mapping System</a></p><p>But as elegant as this solution is, it leaves missing a critical part of geographic analysis: the ability to explore causes and solutions. I think that we need to think about data-informed public participation as requiring a broader range of tools. Almost 50 years ago, Sherry Arnstein termed this a “<a href="https://lithgow-schmidt.dk/sherry-arnstein/ladder-of-citizen-participation.html">ladder of participation</a>.” I propose that its time to start extending this ladder.</p><p>OmniSci is an amazing open source tool for geo data, supporting extremely fast operations on enormous datasets. But before using it, you must of course learn how to import your geodata into the database. In this post, I’ll talk about point data input, using new methods available in OmniSci 4.2. The same methods work very similarly with other geodatatypes. So in this post, I’ll review the best current supported workflows, along with a few tips and tricks.</p><p><strong>Downloading TIMS Data</strong></p><p>As described, Cal Berkeley’s TIMS web site lets you visualize California accident data elegantly online. Fortunately, the tool also allows CSV downloads of its primary data. (A free user account is required)</p><p>If you’d like to follow along, after registering, go to the main ‘SWITRS’ mapping application.</p><p>For example, we can request data for all years, limited to bicycle collisions in Berkeley.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_9wYKFXHzQwwHFNac7xtmQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O_C8qonLBy3Uc6l57IouCw.png" /><figcaption>Result of requesting all bicycle accident points for Berkeley, California for 3 years 2015–2017</figcaption></figure><p>With this selection in place, go to the Tools menu and request a download…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/390/1*Vpe7gmc4AOg-IfJYCbFSUw.png" /><figcaption>Downloading Collisions Data from TIMS</figcaption></figure><p>A secondary dialog box appears asking for optional further specifications. In our case, the defaults were fine.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jzRL6Z-pDU-IMlC2hbDirA.png" /><figcaption>Confirming download defaults (mapped collisions, with data file type of collisions)</figcaption></figure><p>With these steps done, you should have a nice CSV file on your disk. You could open this in most any spreadsheet, since it is not particularly large in terms of record count. It does have an impressive number of columns — 80 or so. If you scroll all the way to the right in your CSV reader of choice, you’ll find some geo attributes. The first thing to notice is that the “LATITUDE” and “LONGITUDE” fields have missing records, and that “POINT_X” and “POINT_Y” appear to have data for those records. If you squint, you’ll also notice that the values are actually different. The TIMS help system does provide access to a full data dictionary, confirmed that these represent WGS84 coordinates. However it is not explicit about the difference between these fields.</p><p><a href="https://tims.berkeley.edu/help/SWITRS.php#Codebook">https://tims.berkeley.edu/help/SWITRS.php#Codebook</a></p><p>The obvious choice is to try ingesting the “POINT_*” fields and to ignore the LATITUDE, LONGITUDE fields.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*zp4F_Oz_Mf6B01okQHnBUA.png" /></figure><p>OmniSci 4.2 has many different ways to import point geodata, and the rules for what is valid vary slightly. The four main methods are:</p><ol><li><strong><em>Import via Immerse Interface</em></strong></li></ol><p>This can be as easy as “drag and drop.” OmniSci supports three major geo file formats: .geojson, .shp, .csv. It also supports various auxiliary files and compressions/bundlings, notably zip and gzip archives. Due to the potential large files sizes for geoJSON and CSV, and the requirement of mutiple files in the shapefile set, I’d recommend always recommend working with compressed files.</p><p>If your geodata are ‘clean’ and a few MB or less, then you can easily upload files from your local machine. There are three limitiations to note: (1) no “null” or “empty” geographies are allowed; (2) no “multipoint” or “multilinestring” features are allowed and (3) mixed feature types are not supported within a single file (for example, mixing of linestrings and multilinestrings). All of these are quiet reasonable limitations, but you will find files ‘in the wild’ which violate one or more of these requirements. For point files derived from address-matching, for example, it is common to have a few percent error rate, leading to records with null geometry. This is easily fixable — but you’ll need to do some cleanup before importing, as I’ll show below.</p><p>If your data are large, a very nice recent addition is that files and archives can be read directly from Amazon s3 storage (public or private). This avoids “time outs” on very large browser uploads and is also a great way to share data with colleagues.</p><p>2) <strong><em>Import using “COPY FROM” mapdql</em></strong></p><p>OmniSci also supports a variant of SQL called mapdql. This can be used to import files on the server, including very large files. I’ve found that it is particularly good at parsing huge GeoJSON files quickly (even ones which don’t open in conventional GIS due to memory constraints).</p><p>But the main added capability in Mapdql is multicolumn support. Basically, if your dataset contains sequential columns of either latitude, longitude or the reverse, then these can be imported an merged into a Point geometry on-the-fly. This is extremely convenient for a large subset of CSV files with pre-existing lat/lon columns. The down side to “COPY FROM” when used with CSV files (as oppossed to GeoJSON or shape files), is that it requires a full database schema to be defined in advance. If you have a simple table of a few columns, this is not a big deal. However, this does become very tedious or require further automation in the case of dozens to hundreds of columns.</p><p><strong><em>3) via JDBC API</em></strong></p><p>This method is brand new to version 4.2 of OmniSci. We will discuss it in a future blog post, but its available now if you need it!</p><p><strong><em>4) Using pymapd Python library</em></strong></p><p>The mapdql method above requires direct command-line server access, which is not provided with OmniSci’s “Cloud” instances. However any mapdql code can easily be run using standard database connectors. The python version is known as pymapd, and works well within jupyter notebook environments.</p><p><strong><em>Geo Data Clean Up with Python/Pandas</em></strong></p><p>As noted above, sometimes geodata needs a bit of cleanup before import. Standard Python and Pandas techniques work very well here.</p><p>First, start by creating a pandas dataframe from the CSV file. The only trick here is the date parser for the 3rd and 5th columns — this is needed so that pandas can correctly recognize the non-standard date representations (see <a href="https://randyzwitch.com/omnisci-pymapd-etl/">Randy Zwitch’s “Using pandas and pymapd for ETL into OmniSci” blog</a> for another example).</p><pre>import pandas as pd<br>import datetime</pre><pre>collisions_csv = ‘BerkeleyCollisions.csv’<br>collisions = pd.read_csv(collisions_csv, parse_dates=[3,5], <br> date_parser= lambda x: datetime.datetime.strptime(x, “%Y-%m-%d”))<br>collisions.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U5xUf_KkCENBxqtAYS-WFw.png" /><figcaption>Pandas dataframe from CSV Collisions data</figcaption></figure><p>Next, clean up the column name in this particular dataset which is a reserved keyword.</p><pre># issue: INTERSECTION is a reserved keyword, <br># even in column name context<br>if (&#39;INTERSECTION&#39; in collisions.columns):<br>    collisions.rename(columns={&#39;INTERSECTION&#39;: &#39;CORNER&#39;}, inplace=True)</pre><p>With those cleanup chores done, we can simply export back out to a cleaned CSV. (Note: setting index=False just avoids creating a redundant column we don’t need.)</p><pre>outcsv = &#39;BerkeleyCollisions_clean.csv&#39;<br>collisions.to_csv(outcsv, index=False)</pre><p>Now, we’re ready to work on database import. I use a standard utility function to connect to OmniSci, with a little error checking added in:</p><pre>import sys,traceback<br>from pymapd import connect<br>con = connect(user=&quot;mapd&quot;, password=&quot;HyperInteractive!&quot;, host=&quot;localhost&quot;, dbname=&quot;mapd&quot;)</pre><pre>def mapdql(query):<br>    if con is None: # refresh if needed<br>        print(&#39;Connection bad or stale&#39;)<br>        return None<br>    try:<br>        print(&#39;Executing query: {}&#39;.format(query))<br>        results = con.execute(query)<br>        return results<br>    except:<br>        print(&#39;Exception executing query&#39;)<br>        a,b,c = sys.exc_info()<br>        for d in traceback.format_exception(a,b,c) :<br>           print (d)<br>        return None</pre><p>With that function initialized, we can execute any legal SQL, getting back some feedback in case of errors. But for mapdql calls beyond ‘execute’, it is simpler to just call directly, looking for any unexpected results manually.</p><p>So as a last step, we generate an appropriate COPY FROM statement and execute it. This is straightforward, but a bit of extra work is required to create a single-quoted absolute path.</p><pre>import os<br>pwd_list = !pwd<br>cwd = pwd_list[0]</pre><pre>table_name = &#39;bike_collisions&#39;<br>full_path_csv_source = os.path.join(cwd, collisions_csv)<br>query = &quot;COPY {} FROM &#39;{}&#39;;&quot;.format(table_name, full_path_csv_source)<br>result = mapdql(query)</pre><p>OK, now let’s take a look in OmniSci’s online data visualization tool, known as Immerse. Here are our initial results (importing all collisions, and not just bike-related for comparison):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0IIwEp-mBa02CuFQe6XVXw.png" /><figcaption>Final Result in Immerse</figcaption></figure><p>Conclusions</p><p>Importing data between systems very commonly brings up some crazy edge cases. This is particularly the case with “geo” embedded in CSV, since so many variations are possible and metadata is missing by default. However, with basic python skills, you can easily accomplish any tweaks needed to extract, transform and load most any geospatial dataset.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e61ff7f796b1" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>