To your question, 4.0 doesn’t have the binary constructor for points
st_mkpoint (see here for usage in Postgis). To achieve what you’d like, you’ll have to import the point data as a bona-fide point column and not as lon/lat floats or doubles. However we are planning on adding
st_mkpoint in an upcoming release to allow this use case.
Joins (assuming you have a table with a point type and a table with a poly type) indeed possible, although with a few kinks and caveats that we hope to work out in future releases.
First off, you need to enable loop joins via the
--allow-loop-joins argument to
mapd_server. Second, you’ll get the best performance if the poly table is on the right of the join, i.e.
SELECT COUNT(*) FROM point_table, poly_table WHERE ST_CONTAINS(poly_col, point_col). Currently though we reorder tables for joins such that the largest tables get pushed to the left. So if your point table is smaller than your poly table, you should make sure to a) put it on the left side of the join from clause, and b) set the
Also currently we don’t allow an update via a subquery, which is what you would need to enrich a point table with poly ids, but this is something we are working on as well.
Final caveat is that since MapD does not yet support dynamic spatial hashing (or spatial indexes), you will be doing a brute force loop join between the two tables. Due to the computation power of GPUs, this actually works pretty well when the cartesian cardinality of the two tables is reasonable (i.e. billions, not necc trillions), i.e. 10M points agains 3K counties, but even GPUs can’t beat physics as the point or poly tables get very large. Adding dynamic spatial hashing is something else we’re investigating currently.
In summary, currently the spatial support is best used for things like filtering all points in a single poly, and of course for rendering. That said, more complex things like joins do work, and our plan is to make them both easier to use and significantly more performant in upcoming releases.