Search…
Migrating Polygon Rendering for Custom Clients Upgrading to OmniSci 4.0
If you imported polygon data into MapD 3.x for a custom app and are rendering and hit-testing that data, upgrading to OmniSci 4.0 or later and reimporting your polygon data breaks your rendering and hit-testing code because the storage model is different.
Use the following guide to migrate your existing polygon Vega-generation and hit-testing code to be compatible with OmniSci 4.0.

Polygons in MapD 3.x

In MapD 3.x, importing polygon data is considered BETA functionality and intended for rendering purposes only. You cannot perform geospatial queries against that data, and the projection is assumed to be Web Mercator. On import, four columns are generated to assist with rendering the polygons:
    omnisci_geo_coords: Array of the x/y coordinate pairs in Web Mercator projected space.
    omnisci_geo_indices: Array of indices into the omnisci_geo_coords array representing the triangulation of the polygon vertices for rendering.
    omnisci_geo_linedrawinfo: Array of a struct with the offsets/sizes for each polygon in omnisci_geo_coords.
    omnisci_geo_polydrawinfo: Array of a struct with the offsets/sizes for each polygon in omnisci_geo_indices.

Polygon Vega Rendering

Polygon rendering in MapD 3.x requires Vega code that maps the Web Mercator-projected vertices in omnisci_geo_coords to screen-space pixel locations. That mapping is done via a linear Vega scale; for example:
1
width: 1183,
2
height: 1059,
3
data: [
4
{
5
name: "polys",
6
format: "polys",
7
sql: "SELECT rowid from zipcodes_2017"
8
}
9
],
10
scales: [
11
{
12
name: "proj_x",
13
type: "linear",
14
domain: [-13644429.98465946, -13592109.8239938],
15
range: "width"
16
},
17
{
18
name: "proj_y",
19
type: "linear",
20
domain: [4524477.448216146, 4570953.288472923],
21
range: "height"
22
}
23
],
24
marks: [
25
{
26
type: "polys",
27
from: {
28
data: "polys"
29
},
30
properties: {
31
x: {scale: "proj_x", field: "x"},
32
y: {scale: "proj_y", field: "y"},
33
fillColor: "red"
34
}
35
}
36
]
Copied!
The scales proj_x and proj_y, highlighted in the code above, map Web Mercator coordinates to pixel coordinates. The domain for proj_x are the longitude values [-122.57, -122.1], and, the domain for proj_y are the latitude values [37.61, 37.94].

Polygon Hit-testing

When generating an SVG of polygon/multipolygon in the client upon a successful hit-test, you needed to do the following:
    Call the get_result_row_for_pixel(...) method via a mapd.thrift.js javascript module. The omnisci_geo_coords and omnisci_geo_linedrawinfo columns are used as part of the table_col_names argument to get the vertex coordinates and size/offsets for the polygons on a successful hit-test.
    NOTE: If using the mapd-connector JavaScript module, you likely would call the getResultRowForPixel() wrapper method and pass the column information via the tableColNamesMap argument instead.
    After a successful hit-test, you likely would have iterated over the data from the omnisci_geo_coords and omnisci_geo_linedrawinfo columns to manually build an SVG.
    NOTE: The data from omnisci_geo_coords would be in Web Mercator coordinates, which would need to be converted to screen-space coordinates at some point, likely during building or drawing of the SVG.

Polygons in OmniSci 4.0

In OmniSci 4.0, polygon data is stored in a more general format that can be both queried (using geospatial functions such as st_contains and st_distance) and rendered. Therefore, the vertices of the polygon are no longer stored in Web Mercator; instead, they are stored in WGS84 coordinates if the polygons were imported with a spatial reference system.

Polygon Vega Rendering in OmniSci 4.0

Polygons are now imported with a spatial reference system and stored in WGS84 coordinates. So, you apply a cartographic projection to map the longitude/latitude values to pixels. In OmniSci 4.0, you do this using Vega Projections. The following Vega example renders polygons in OmniSci 4.0 using a Mercator cartographic projection:
1
{
2
width: 1183,
3
height: 1059,
4
data: [
5
{
6
name: "polys",
7
format: "polys",
8
sql: "SELECT rowid from zipcodes_2017"
9
}
10
],
11
projections: [
12
{
13
name: "merc",
14
type: "mercator",
15
bounds: {
16
x: [-122.57, -122.1],
17
y: [37.61, 37.94],
18
}
19
}
20
],
21
marks: [
22
{
23
type: "polys",
24
from: {
25
data: "polys"
26
},
27
properties: {
28
x: {field: "x"},
29
y: {field: "y"},
30
fillColor: "red"
31
}
32
transform: {
33
projection: "merc"
34
}
35
}
36
]
37
}
Copied!
Currently, Web Mercator is the only Vega projection supported.

Polygon Hit-testing

During polygon import in OmniSci 4.0, a publicly accessible geo column is created. This column name defaults to omnisci_geo, but can be named differently. This is the only column required to extract the polygon geometry for hit-testing. When hit-testing using omnisci_geo, a WKT string is returned representing the polygon geometry, instead of an arrow of coordinates.
The polygon vertices in the WKT string are in WGS84 coordinates if the polygons were imported with a spatial reference system.

Converting WKT to SVG

Now that the geometry data is returned in a common format, many tools and libraries are available to convert WKT to SVG. In OmniSci Immerse, two libraries are used to perform the conversion:
    1.
    wellknown - Converts WKT to geojson.
    2.
    d3-geo - Uses the d3.geoPath() function to convert the geojson to an SVG. See d3-geo Paths for more information. This handles the projection to screen space as well.

Migrating from 3.x to 4.0

After reimporting your polygon geometry in OmniSci 4.0, the vertices are now stored in WGS84 coordinates. To migrate polygon rendering from OmniSci 3.x to OmniSci 4.0:
    1.
    Add a new projection block to your Vega code using a Mercator projection, following this template:
    1
    projections: [
    2
    {
    3
    name: "merc",
    4
    type: "mercator",
    5
    bounds: {
    6
    x: [, ],
    7
    y: [, ],
    8
    }
    9
    }
    10
    ]
    Copied!
    2.
    Remove any unreferenced WebM Mercator-to-pixel scales. Although not required because any unused scales are ignored, removing the scales keeps your Vega code clean.
    3.
    Reference the new Mercator projection in a transform block in your polys marks; for example:
    1
    {
    2
    type: "polys",
    3
    from: {data: "polys"},
    4
    properties: { … }
    5
    transform: {
    6
    projection: "merc"
    7
    }
    8
    }
    Copied!
To migrate your polygon hit-testing and SVG generation from MapD 3.x to OmniSci 4.0:
    1.
    Replace the omnisci_geo_coords and omnisci_geo_linedrawinfo columns with omnisci_geo (or whatever you name the geo colum) in the get_result_row_for_pixel call (or getResultRowForPixel if you use mapd-connector).
    2.
    Convert the WKT returned on a successful hit-test to SVG. You can use one of several exising libraries instead of constructing the SVG manually.

Example: SVG Drawing Code in OmniSci 4.0

Following is the HTML/JavaScript code demonstrating the primary aspects of constructing an SVG on the client in a successful poly hit-test. This snippet is not intended to fully replace your existing code; instead, it shows you the main flow and structure to use when migrating.

Shapefile

The example uses a US zipcodes shapefile from 2017, which can be downloaded as a zip file. If you are using omnisql, run the following command to import the downoaded zip file, replacing <zip path> with the absolute path name where the above zip file can be found:
1
omnisql> COPY zipcodes_2017 FROM '<zip path>' WITH (geo='true');
Copied!
The zipcodes_2017 table has the following structure after import:
1
omnisql> \d zipcodes_2017
2
CREATE TABLE zipcodes_2017 (
3
ZCTA5CE10 TEXT ENCODING DICT(32),
4
AFFGEOID10 TEXT ENCODING DICT(32),
5
GEOID10 TEXT ENCODING DICT(32),
6
ALAND10 BIGINT,
7
AWATER10 BIGINT,
8
omnisci_geo GEOMETRY(MULTIPOLYGON, 4326) ENCODING COMPRESSED(32))
Copied!
Notice that the omnisci_geo column from the import is a MULTIPOLYGON with a spatial reference system of EPSG:4327 WGS 84 and has compressed coordinates.

Libraries

The following libraries are used in this example:

Example Code

1
<!DOCTYPE html>
2
<html lang="en">
3
4
<head>
5
<title>OmniSci Polygon 4.0 SVG Hittest Example</title>
6
<meta charset="UTF-8">
7
<style>
8
path {
9
fill: orange;
10
stroke: #DDD;
11
stroke-width: 1px;
12
}
13
</style>
14
</head>
15
16
<body>
17
<script src="https://d3js.org/d3-selection.v1.min.js"></script>
18
<script src="https://d3js.org/d3-array.v1.min.js"></script>
19
<script src="https://d3js.org/d3-geo.v1.min.js"></script>
20
<script src="js/wellknown.js"></script>
21
<script src="js/browser-connector.js"></script>
22
23
<script>
24
25
function init() {
26
27
/**
28
* Define the server and login configuration
29
*/
30
config = {
31
protocol: <http/https>,
32
hostname: <hostname>,
33
port: <port>,
34
db: <db name>,
35
user: <user name>,
36
password: <user password>
37
}
38
39
/**
40
* Define the width/height of the resulting image
41
* and the longitude/latitude bounds of the mercator projection
42
*/
43
const width = 1183
44
const height = 1059
45
const latlonBounds = [[-122.57, 37.61], [-122.1, 37.94]]
46
47
/**
48
* The name of the geo column with the polygon data. omnisci_geo is the default name
49
*/
50
const geoColumn = "omnisci_geo"
51
52
/**
53
* Below is the example vega to use. The "zipcodes_2017" table used below can be found
54
* here: https://www.census.gov/geo/maps-data/data/cbf/cbf_zcta.html
55
*
56
* You can just wget it using this command:
57
* wget www2.census.gov/geo/tiger/GENZ2017/shp/cb_2017_us_zcta510_500k.zip -O zipcodes_2017.zip
58
*
59
* Then import into OmniSci. Via omnisql, you can run the following command:
60
* omnisql> COPY zipcodes_2017 FROM '<path to>/zipcodes_2017.zip' WITH (geo='true');
61
62
*
63
* These are the columns created:
64
*
65
66
omnisql> \d zipcodes_2017
67
*
68
* CREATE TABLE zipcodes_2017 (
69
* ZCTA5CE10 TEXT ENCODING DICT(32),
70
* AFFGEOID10 TEXT ENCODING DICT(32),
71
* GEOID10 TEXT ENCODING DICT(32),
72
* ALAND10 BIGINT,
73
* AWATER10 BIGINT,
74
* omnisci_geo GEOMETRY(MULTIPOLYGON, 4326) ENCODING COMPRESSED(32))
75
76
*
77
* You will notice that a omnisci_geo column exists and it is a multipolygon with
78
* spatial reference system 4326 (WGS84): http://spatialreference.org/ref/epsg/4326/
79
* You will also notice that the coordinates are compressed by default.
80
*/
81
const vega = {
82
width: width,
83
height: height,
84
data: [
85
{
86
name: "zipcodes",
87
format: "polys",
88
sql: "SELECT rowid, ALAND10 as area from zipcodes_2017"
89
},
90
{
91
name: "stats",
92
source: "zipcodes",
93
transform: [
94
{
95
type: "aggregate",
96
fields: ["area"],
97
ops: [{ type: "quantile", numQuantiles: 8 }],
98
as: ["quantileval"]
99
}
100
]
101
}
102
],
103
projections: [
104
{
105
name: "merc",
106
type: "mercator",
107
bounds: {
108
x: [latlonBounds[0][0], latlonBounds[1][0]],
109
y: [latlonBounds[0][1], latlonBounds[1][1]]
110
}
111
}
112
],
113
scales: [
114
{
115
name: "polys_fillColor",
116
type: "threshold",
117
domain: { data: "stats", field: "quantileval" },
118
range: [
119
"rgb(0,0,255)",
120
"rgb(36,0,219)",
121
"rgb(72,0,183)",
122
"rgb(108,0,147)",
123
"rgb(147,0,108)",
124
"rgb(183,0,72)",
125
"rgb(219,0,36)",
126
"rgb(255,0,0)"
127
],
128
nullValue: "#cacaca"
129
}
130
],
131
marks: [
132
{
133
type: "polys",
134
from: { data: "zipcodes" },
135
properties: {
136
x: { field: "x" },
137
y: { field: "y" },
138
fillColor: { scale: "polys_fillColor", field: "area" },
139
strokeColor: "black",
140
strokeWidth: 1,
141
lineJoin: "round"
142
},
143
transform: { projection: "merc" }
144
}
145
]
146
}
147
148
// Now create a new connection to OmniSci
149
var conn = new OmniSciCon()
150
.protocol(config.protocol)
151
.host(config.hostname)
152
.port(config.port)
153
.dbName(config.db)
154
.user(config.user)
155
.password(config.password)
156
.connect(function (error, con) {
157
158
if (error) {
159
throw error
160
}
161
162
// run a serialized renderVega() call and immediately get the results
163
var results = con.renderVega(1, JSON.stringify(vega))
164
165
// get the resulting PNG and build a base64 blob data URL
166
var blobUrl = 'data:image/png;base64,' + results.image;
167
168
// Now add the PNG to an img element, parented under a parent div
169
const body = d3.select(document.body)
170
var div = body.append("div").style("position", "relative")
171
var img = div.append("img").attr("src", blobUrl).attr("alt", "backend-rendered png")
172
173
// Now do a hit-test check. Note that in the BE, the lower left-hand corner of the image
174
// is pixel (0, 0), so we have to invert y.
175
// The pixel used here: (326, 514) hits the zipcode 94117 for this render
176
con.getResultRowForPixel(
177
1, // widgetId
178
new TPixel({ x: 326, y: height - 544 - 1 }), // The pixel to hit-test, with Y inverted
179
{ zipcodes: [geoColumn] }, // Get the polygon column data on a successful hit-test
180
// 'omnisci_geo' is the default polygon geo column name
181
(err, data) => { // Provide an asynchronous callback for the hit-test results
182
if (err) {
183
throw err
184
}
185
186
// check if we got a successful hit-test result. The rowid >= 0 if that's the case
187
if (data.length && data[0].row_id >= 0) {
188
if (!data[0].row_set.length) {
189
throw new Error("Invalid result set returned from poly hit-test", data);
190
}
191
192
// the polygon geo column will be a wkt string:
193
// https://en.wikipedia.org/wiki/Well-known_text
194
const wkt = data[0].row_set[0][geoColumn]
195
if (typeof wkt !== "string") {
196
throw new Error(
197
`Cannot create SVG from geo polygon column "${geoColumn}". The data returned is not a WKT string. It is of type: ${typeof wkt}`
198
)
199
}
200
201
// We'll use the wellknown library from mapbox to convert the wkt string to geojson
202
// https://github.com/mapbox/wellknown
203
const geojson = wellknown.parse(wkt)
204
if (geojson.type !== "MultiPolygon" && geojson.type !== "Polygon") {
205
throw new Error(`Cannot create SVG from geojson ${geojson}. Currently only Polygon/MultiPolygon is supported`)
206
}
207
208
// Now we'll build out a d3 mercator projection used to position the resulting SVG into
209
// screen space coordinates
210
211
// the center of the projection will be the center of the defined lat/lon bounds
212
const centerLon = latlonBounds[0][0] + (latlonBounds[1][0] - latlonBounds[0][0]) / 2
213
const centerLat = latlonBounds[0][1] + (latlonBounds[1][1] - latlonBounds[0][1]) / 2
214
215
// now setup a default projection centered in the middle of our div on the middle of our
216
// defined lat/lon bounds
217
const projection = d3.geoMercator()
218
.center([centerLon, centerLat]) // Center the projection on the center of our bounds
219
.scale(1) // set a default scale for the projection.
220
// We'll calculate a real scale later
221
.translate([width / 2, height / 2]) // Translate the center of the projection to be
222
// in the middle of our div, otherwise it'll be in
223
// the top-left corner
224
225
// now get the X coordinates of the pixel locations of our left/right bounds edge
226
// using the default projection. We'll use this as a base of our scale
227
const baseMinLon = projection(latlonBounds[0])[0]
228
const baseMaxLon = projection(latlonBounds[1])[0]
229
230
// Now reset the projection's scale. This will now be in line with the
231
// vega render
232
projection.scale(width / (baseMaxLon - baseMinLon))
233
234
// now build the svg - build the parent svg view
235
const svg = div.append("svg")
236
.style("position", "absolute")
237
.style("left", 0)
238
.style("width", width)
239
.style("height", height)
240
241
// now build the path. We're using d3.geoPath to automatically build the
242
// svg path string.
243
svg.append("path")
244
.datum(geojson)
245
.attr("d", d3.geoPath(projection))
246
}
247
}
248
)
249
});
250
}
251
252
document.addEventListener('DOMContentLoaded', init, false);
253
</script>
254
</body>
255
256
</html>
Copied!

Result

The previous code results in the following:
Last modified 1yr ago