k255
commented
8 years ago Hi, thanks for your interest in drill-gis. Actually drill-gis uses wkb as geometry representation by adapting varbinary data type. The problem is probably how drill jdbc handles data received from postgresql. Take a look at your results again with this query:
select cast(current_location as varchar(255)) from postgis.public.device_location_wbk;
or even
select cast(current_location as varchar(255)) from postgis.public.device_location;
as you see there are the results, but probably the data type is not handled by jdbc (the connection uses just postgres without postgis extensions).
If you work with just simple POINT type then a workaround is to use wkt or just lat lon and convert it using drill-gis back to geometry like this:
pgsql:
create view device_location_wkt as select ST_AsText(current_location) as current_location from device_location;
drill:
select ST_GEOMFROMTEXT(current_location) as geom from postgis.public.device_location_wkt;
or using lat lon:
pgsql:
create view device_location_xy as select ST_X(current_location) as current_location_x, ST_Y(current_location) as current_location_y from device_location;
drill:
select ST_Point(current_location_x, current_location_y) as geom from postgis.public.device_location_xy;
That way you receive geometry in drill which you can then pass to other drill-gis ST_ functions.
The way you tried it would be even better but unfortunately there's no straight way to achieve that at the moment. Some time ago I forced drill-gis to use wkb/ewkb string from postgis but it was quite complex (casts and unions). I have some ongoing development to support native postgis ewkb geometries but it's just a prototype right now.
JoseSparano
Great stuff.
However, I am having some problems with it: I am trying to get geometry data from Postgis using drill and later make a join with mongodb.
Here is a simple example of my query: select * from postgis.public.device_location_wkb d where ST_Within( d.current_location, ST_GeomFromText( 'POLYGON((0.0 0.0, 10.0 0.0, 10.0 10.0, 0.0 10.0, 0.0 0.0))' ) );
does not return any results, but is should. I am assuming that drill understands WKB, but it looks it does not.
My test data in PostGIS: CREATE TABLE public.device_location( device_id bigint, current_location geometry(POINT, 4326) NOT NULL, last_modified_date timestamptz NOT NULL, CONSTRAINT device_location_pk PRIMARY KEY (device_id) );
CREATE VIEW device_location_wkb AS SELECT ST_AsBinary(current_location) AS current_location FROM device_location;
INSERT INTO device_location (current_location, last_modified_date ) VALUES ( ST_Transform(ST_SetSRID(ST_Point(1.0,2.0),4326), 4326), now() ) ;