Geospatial Support


#1

I have seen examples/documentation of lat/long and some spatial query samples. Does it support other geometric objects like LineString, MultiLineString,Point, Polygon, WKT formats etc. Any support for spatial queries, spatial joins ?

Appreciate, If you can point to me any documentation that explains this?

Thanking you
Raja Thiruvathuru


Spatial intersections
#2

Hi,

We only have one geospatial specific function currently

http://docs.mapd.com/latest/mapd-core-guide/dml/#geometric-function-support

Keep your eye out for our soon to be released product road map with more detail of what to expect when.

regards


#3

Thanks for the prompt reply. I see you in Immerse you were doing spatial queries like with in a circle, polygon etc. wonder how you were doing it


#4

Hi,

The circle calculations are done with DISTANCE_IN_METERS the polygons are done by generating rather large complex queries based on creating trianges to cover the full polygon and some bounding box checking on the lats and lon involved, Here is an example from a taxi polygon query I ran on our online taxi demo https://www.mapd.com/demos/taxis:

SELECT
date_trunc(week,pickup_datetime) as key0,COUNT(*) AS series_1
FROM taxi_weather_tracts_factual
WHERE
(
   pickup_datetime >= TIMESTAMP(0) '2009-01-01 00:00:02'
   AND pickup_datetime <= TIMESTAMP(0) '2015-12-31 23:59:28'
)
AND
(
   dropoff_longitude >= -74.05329986666602
   AND dropoff_longitude <= -73.75680213328293
)
AND
(
   dropoff_latitude >= 40.583763919385945
   AND dropoff_latitude <= 40.91004871677367
)
AND
(
   dropoff_longitude IS NOT NULL
   AND dropoff_latitude IS NOT NULL
   AND
   (
      UNLIKELY
      (
         dropoff_longitude >= -73.99058729459973
         AND dropoff_longitude <= -73.9624610430501
         AND dropoff_latitude >= 40.771760796312584
         AND dropoff_latitude <= 40.80808958787947
      )
      AND
      (
         (
            (
               (
                  dropoff_longitude-(-73.99058729459973)
               )
               *
               (
                  0.006650120429306128
               )
               -
               (
                  0.019924633004507086
               )
               *(dropoff_latitude-(40.801439467450166)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.98963508039844)
               )
               *
               (
                  0.01681526491086771
               )
               -
               (
                  -0.0009522142012912127
               )
               *(dropoff_latitude-(40.7846242025393)) < 0.0
            )
         )
         AND
         (
            (
               (
                  dropoff_longitude-(-73.98963508039844)
               )
               *
               (
                  0.01681526491086771
               )
               -
               (
                  -0.0009522142012912127
               )
               *(dropoff_latitude-(40.7846242025393)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.97066266159523)
               )
               *
               (
                  -0.02346538534017384
               )
               -
               (
                  -0.018972418803215874
               )
               *(dropoff_latitude-(40.80808958787947)) < 0.0
            )
         )
         OR
         (
            (
               (
                  dropoff_longitude-(-73.98612715921347)
               )
               *
               (
                  0.012863406226713892
               )
               -
               (
                  -0.003507921184976226
               )
               *(dropoff_latitude-(40.771760796312584)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.98544893117386)
               )
               *
               (
                  -0.00044219966962089075
               )
               -
               (
                  -0.0006782280396038232
               )
               *(dropoff_latitude-(40.772202995982205)) < 0.0
            )
         )
         AND
         (
            (
               (
                  dropoff_longitude-(-73.98544893117386)
               )
               *
               (
                  -0.00044219966962089075
               )
               -
               (
                  -0.0006782280396038232
               )
               *(dropoff_latitude-(40.772202995982205)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.98963508039844)
               )
               *
               (
                  -0.012421206557093
               )
               -
               (
                  0.004186149224580049
               )
               *(dropoff_latitude-(40.7846242025393)) < 0.0
            )
         )
         OR
         (
            (
               (
                  dropoff_longitude-(-73.96480564594196)
               )
               *
               (
                  0.000047621361467520273
               )
               -
               (
                  -0.02064328523189829
               )
               *(dropoff_latitude-(40.77215537462074)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.9624610430501)
               )
               *
               (
                  -0.01907982632474159
               )
               -
               (
                  -0.002344602891866998
               )
               *(dropoff_latitude-(40.79123520094548)) < 0.0
            )
         )
         AND
         (
            (
               (
                  dropoff_longitude-(-73.9624610430501)
               )
               *
               (
                  -0.01907982632474159
               )
               -
               (
                  -0.002344602891866998
               )
               *(dropoff_latitude-(40.79123520094548)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.98544893117386)
               )
               *
               (
                  0.01903220496327407
               )
               -
               (
                  0.022987888123765288
               )
               *(dropoff_latitude-(40.772202995982205)) < 0.0
            )
         )
         OR
         (
            (
               (
                  dropoff_longitude-(-73.97066266159523)
               )
               *
               (
                  -0.01685438693399277
               )
               -
               (
                  0.008201618545129463
               )
               *(dropoff_latitude-(40.80808958787947)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.98963508039844)
               )
               *
               (
                  0.02346538534017384
               )
               -
               (
                  0.018972418803215874
               )
               *(dropoff_latitude-(40.7846242025393)) < 0.0
            )
         )
         AND
         (
            (
               (
                  dropoff_longitude-(-73.98963508039844)
               )
               *
               (
                  0.02346538534017384
               )
               -
               (
                  0.018972418803215874
               )
               *(dropoff_latitude-(40.7846242025393)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.9624610430501)
               )
               *
               (
                  -0.006610998406181068
               )
               -
               (
                  -0.027174037348345337
               )
               *(dropoff_latitude-(40.79123520094548)) < 0.0
            )
         )
         OR
         (
            (
               (
                  dropoff_longitude-(-73.98544893117386)
               )
               *
               (
                  0.012421206557093
               )
               -
               (
                  -0.004186149224580049
               )
               *(dropoff_latitude-(40.772202995982205)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.9624610430501)
               )
               *
               (
                  -0.01903220496327407
               )
               -
               (
                  -0.022987888123765288
               )
               *(dropoff_latitude-(40.79123520094548)) < 0.0
            )
         )
         AND
         (
            (
               (
                  dropoff_longitude-(-73.9624610430501)
               )
               *
               (
                  -0.01903220496327407
               )
               -
               (
                  -0.022987888123765288
               )
               *(dropoff_latitude-(40.79123520094548)) < 0.0
            )
            =
            (
               (
                  dropoff_longitude-(-73.98963508039844)
               )
               *
               (
                  0.006610998406181068
               )
               -
               (
                  0.027174037348345337
               )
               *(dropoff_latitude-(40.7846242025393)) < 0.0
            )
         )
      )
   )
)
GROUP BY key0
ORDER BY key0

regards


#5

hi @dwayneberry sorry for bothered you, could i ask, where did you get this value 0.006650120429306128 , 0.019924633004507086 ? i’m confuse when implement it to query

regards


#6

Hi @rejanuiss ,

This is from the triangulation algorithm we use in the frontend to take a user-drawn polygon and turn it into a set of triangles, which we then run a point-in-triangle intersection operation for each on. You could re-implement this yourself, but your best bet might be waiting on the geospatial operators that will land in the next release.

Regards


#7

Here, I have write a simple code for generating polygon query in python.
polygon_query.py.zip (1.3 KB)

In this code, you should provide minimum and maximum longitude in your map window, minimum and maximum latitude in your map window, and list of polygon points in regards to their click order.

I have tried this query and worked for a simple polygon. Meanwhile, it still has some bugs in determining the set of triangles for complicated polygon that has many small angles or even overlapping lines.
Hope this helps!


#8

@thiruvathuru just in case you didn’t see it, we announced the release of MapD 4.0 this week. It has support for linestring, polygon, multipolygon, and more.


#9

Wonderful news. Will give it a try


#10

Is this available in the docker images? When running the below query, I get Exception: Function ST_GeomFromText not supported. Am I doing something wrong?

select st_geomfromtext('POINT(1 2)') from my_table;

(I am selecting from the table because otherwise I get Exception: Non-empty LogicalValues not supported yet).

Edit: Seems I need to be querying a table with a geometry column to make it work.


#11

Hi @Josh,

The Docker images with version 4 is available and supports Geospatial functionality.
Not sure what you are trying to do with ST_GeomFromText, but here are couple of commands using this function that works for me.

mapdql> \d sf_facilities
CREATE TABLE sf_facilities (
facility_i TEXT ENCODING DICT(32),
facility_n TEXT ENCODING DICT(32),
deptname TEXT ENCODING DICT(32),
dept INTEGER,
mapd_geo GEOMETRY(POINT, 4326) ENCODING COMPRESSED(32))

mapdql> select * from sf_facilities limit 5;
facility_i|facility_n|deptname|dept|mapd_geo
0000000862|HYDE ST PUMP STATION|Recreation and Park|42|POINT (-122.420980326096 37.8073850962368)
0000000917|SFGH - Building 40 (COR. 40-9)|Public Health|83|POINT (-122.404416009041 37.7556322923655)
0000000459|HUNTERS PT COMM. YOUTH (EAST)|Mayor|25|POINT (-122.37999265193 37.7338732489077)
0000000510|African American Art & Cultural Complex|Arts Commission|28|POINT (-122.429235411076 37.7782807395692)
0000003031|SFIA-BOARDING AREA D|Airports Commission|27|POINT (-122.395708804203 37.6218492386964)

mapdql> select ST_Distance(mapd_geo, ST_GeomFromText(‘POINT(1 2)’, 4326)) from sf_facilities limit 5;
EXPR$0
128.4863050256937
128.486980462198
128.4878502130711
128.4881490285425
128.4962472420748

mapdql> select count(*) from sf_facilities where ST_Distance(mapd_geo, ST_GeomFromText(‘POINT(1 2)’, 4326)) > 128.4881;
EXPR$0
778

Regards,
Veda