Forum

Discussions specific to version 8.1
Multiple Poster
Offline
User avatar
Posts: 2
Joined: Fri Oct 31, 2014 8:01 pm

Does Kognitio support GeoSpatial data

by aabraham » Fri Oct 31, 2014 8:04 pm

We're in the process of doing a POC w/ Kognitio 8 and having an existing database (Postgres), where most of our data is geoSpatial data, so we're using PostGIS. Does Kognitio support that? If so, how would I go about setting it up? I tried searching in your knowledge base and forum and haven't seen anything relative to what we need.
Thanks in advance for any help that can be provided.
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Does Kognitio support GeoSpatial data

by markc » Fri Oct 31, 2014 10:18 pm

• POINT(x,y) - takes a lat/long pair and converts it to an internal representation (varbinary)
• POLYGON(x1,y1,x2,y2,x3,y3,…) - takes a sequence of lat/long pairs that together define an arbitrary solid shape, e.g. a particular region on a map, and converts them to an internal representation (varbinary);
• INSIDE_POLYGON(POINT,POLYGON) – takes a POINT and a POLYGON and decides if the point is inside (=1) or outside (=0) the polygon, i.e. is the point inside the shape defined by the polygon?

For example:

select top 50 p.first_listing_property_id,p.postcode1,p.postcode2
from property p
where inside_polygon(point(p.latitude,p.longitude),polygon(51.4484, 0.1735, 51.4295, 0.2132, 51.4327, 0.2659, 51.4534, 0.2745, 51.4661, 0.1986))=1
order by 1;

FIRST_LISTI|POST|POST
6272893|DA2 |6RL
6466582|DA1 |2TU
etc.

This query lists out the property postcodes (zip codes) that lie within the simple polygon defined by the 5 lat/long pairs. Each property has its own lat/long co-ordinates and we use the INSIDE_POLYGON function to decide if that property is in the shape defined by the polygon or not.

Polygons can be stored in varchar fields, so it is possible to look up a polygon and then filter the query based on the polygon returned. For example:

insert into region(id,name,poly)
values (1,’London’,’50.76091939844,-3.321462346120,50.81215003607,-3.404548210962,…’);
etc.

then do a query like this:

select…
from property p
where p.viewing_date between date '2011-01-01' and date '2012-01-01'
and inside_polygon(point(p.latitude,p.longitude),(select polygon(r.poly) from region r where r.id=1))=1; -- London region

Internally, the POLYGON function will convert the varchar string containing the lat/long pairs to the desired varbinary representation.

The INSIDE_POLYGON function is a relatively expensive operation, so there are a couple of tips for maximising its performance:

1.) Always put the INSIDE_POLYGON function last in the WHERE clause after all the other filters. This ensures that only rows that meet all the other ‘AND’ clauses are passed to the expensive INSIDE_POLYGON function. In the example above the viewing_date clause purposely comes before the INSIDE_POLYGON function for this reason;
2.) The INSIDE_POLYGON function has a second form where you can pass it a lat/long pair directly, e.g. ‘… and inside_polygon(p.latitude,p.longitude,(select polygon(r.poly) from region r where r.id=1))=1;’. This avoids the expense of running the POINT function for each row;
3.) The INSIDE_POLYGON function has an in-built ‘bounding box’ test that checks whether an x,y pair is likely to be in the polygon based on that polygon’s min and max x/y values, i.e. if the x value of the point you are checking is less than the minimum x value of any part of the polygon, then there is no point in fully checking to see if the point is inside the polygon, as by definition, it can’t be. This ‘bounding box’ check helps to keep the overhead of the function down. However, it is possible to return the bounding box X/Y coordinates to the query and do the filtering yourself, which might be beneficial in certain circumstances. The functions are POLYGON_XMIN(<polygon>), POLYGON_XMAX(<polygon>), POLYGON_YMIN(<polygon>) and POLYGON_YMAX(<polygon>). Note that unlike the INSIDE_POLYGON function, you can’t pass strings to these MIN/MAX functions, so you would need to say something like POLYGON_XMIN(POLYGON(’51.444,1.2,…’)) instead. To date, we haven’t really used these functions much.

The internal varbinary representation of a polygon can be up to 32k in length and each x,y pair takes 16 bytes, so you can have a maximum of 2,000 points in a polygon. We have done demonstrations where the largest polygon we have has about 1,700 points, which is pretty large.
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Does Kognitio support GeoSpatial data

by markc » Sat Nov 01, 2014 3:11 pm

Forgot to mention in the previous reply that these are part of the geometry plugin, so you will need to ensure that module is loaded to use those functions (see documentation for how to load plugins).
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Does Kognitio support GeoSpatial data

by markc » Mon Nov 03, 2014 1:01 pm

SQL Guide has been updated to include references to the geospatial functions in the plugin functions section.
Reply with quote Top
Multiple Poster
Offline
User avatar
Posts: 2
Joined: Fri Oct 31, 2014 8:01 pm

Re: Does Kognitio support GeoSpatial data

by aabraham » Mon Nov 03, 2014 3:47 pm

What of multipolygon? So again, we're using Postgres/PostGIS ... it'd be nice to know that Multipolygon is supported in this case.
Reply with quote Top
Multiple Poster
Offline
User avatar
Posts: 2
Joined: Mon Jun 10, 2013 2:22 pm
Location: Bracknell

Re: Does Kognitio support GeoSpatial data

by PaulG » Tue Nov 04, 2014 10:04 am

Note that wxloader supports WKT POLYGON field definition for bulk data load of raw polygon data e.g.

ref: http://en.wikipedia.org/wiki/Well-known ... ic_objects

To load into table:
create table my_polygons (polykey int, poly varbinary(32000));

Data of format:
246981069,POLYGON((-115.287411189375 36.2913701270631,-115.287431853583 36.2913833394115,-115.287452525249 36.2914105961587,-115.287543514413 36.2914094292107,-115.287544327732 36.2913071492901,-115.287388187405 36.2913047217802,-115.287389682044 36.2913648094762,-115.287411189375 36.2913701270631))
246981070,POLYGON((-90.5842939923568 38.5918854269449,-90.5841687057013 38.591968882189,-90.5841360173634 38.5920268097654,-90.584221693519 38.5924209127939,-90.584338425698 38.5924287712196,-90.5847394189978 38.5923913780779,-90.5846649862093 38.5918821700529,-90.5842939923568 38.5918854269449))
Paul Groom - Chief Innovation Officer, Kognitio
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron