jooq: How to deal with POINT MySQL datatype

Question

I have the following table in MySQL

CREATE TABLE `place` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) DEFAULT NULL,
  `STREET_NUMBER` int(11) DEFAULT NULL,
  `ROUTE` varchar(255) DEFAULT NULL,
  `LOCALITY` varchar(255) DEFAULT NULL,
  `ADM_AREA_L1` varchar(255) DEFAULT NULL,
  `ADM_AREA_L2` varchar(255) DEFAULT NULL,
  `COUNTRY` varchar(100) DEFAULT NULL,
  `POSTAL_CODE` varchar(10) DEFAULT NULL,
  `FORMATTED_ADDRESS` varchar(1000) DEFAULT NULL,
  `LOCATION` point DEFAULT NULL,
  `CLOSING_TIME` time DEFAULT NULL,
  `CREATION_DATE` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It has a field of type POINT, to make geolocation queries over that field. The Pojo generated by jooq is the following:

public class Place implements java.io.Serializable {
    private static final long serialVersionUID = -1847682719;

    private java.lang.Long     id;
    private java.lang.String   name;
    private java.lang.Integer  streetNumber;
    private java.lang.String   route;
    private java.lang.String   locality;
    private java.lang.String   admAreaL1;
    private java.lang.String   admAreaL2;
    private java.lang.String   country;
    private java.lang.String   postalCode;
    private java.lang.String   formattedAddress;
    private java.lang.Object   location;
    private java.sql.Time      closingTime;

    // getters and setters
}

This is the kind of queries that I want to execute:

Inserting a new record. The sql for the POINT datatype would be:
```
INSERT INTO PLACE(LOCATION) VALUES(POINT(1,1));
```
Getting latitude and longitude from the POINT datatype. The sql would be:
```
SELECT X(LOCATION), Y(LOCATION) FROM PLACE;
```

Quering places withing a distance range. The sql would be:

SELECT *
FROM PLACE
WHERE ST_WITHIN(LOCATION, ENVELOPE(LINESTIRNG(
POINT(@lon - @distance / abs(cos(radians(@lat)) * 69), @lat - (@distance / 69)),
POINT(@lon + @distance / abs(cos(radians(@lat)) * 69), @lat + (@distance / 69))
)))
ORDER BY ST_DISTANCE(POINT(<lon>, <lat>), LOCATION);

Do I have to create a CustomField for each situation (inserting, getting latitude and longitude, quering by distance range, ordering by distance)?

In case of creating a CustomField. CustomField of what? Because the LOCATION field generated by jooq in Place table is of type DataType<Object>.

I have tried to execute an insert setting the LOCATION field and it does not compile because the execute() method is undefined for the Field<Object>.

Lukas Eder Lukas Eder · Accepted Answer · 2014-03-03T07:47:33

Spatial / GIS extensions to the various SQL dialects are not yet supported in jOOQ 3.3. The relevant issue for this is #982

jooq: How to deal with POINT MySQL datatype

1 Answers