Extras for geospatial support

Unfortunately, Exposed doesn't natively support PostGIS functionality such as geometry types or geospatial requests. This is an excellent opportunity to use Extension functions by Kotlin and fill the missing support without modifying the original source.

Adding support for location

The location data for the message is in the form of coordinates. To store such a data structure in the database, we define PointColumnType to store and get the location data for the message:

     * It represents the point column type for Exposed
     */
    class PointColumnType(private val srid: Int = 4326) : ColumnType()    
    {
      override fun sqlType() = "GEOMETRY(Point, $srid)"
      override fun valueFromDB(value: Any) = if (value is PGgeometry)    
       value.geometry else value
      override fun notNullValueToDB(value: Any): Any {
        if (value is Point) {
          if (value.srid == Point.UNKNOWN_SRID) value.srid = srid
          return PGgeometry(value)
        }
      return value
      }
    }

And add an Extension function to directly use Point Column in our Messages object:

    /**
      * Extension function to get point column type from the table
      */
    fun Table.point(name: String, srid: Int = 4326): Column<Point>
     = registerColumn(name, PointColumnType())

Adding support for location-bound queries

One more extra geospatial functionality we need is firing SQL queries within a selected area of the map; that is a bounded box. For that we need to create an expression for the required column type, PGbox2d, which represents the bounded location:

    /**
      * Special type to represent the box and if location of message
      * is inside the specified box
      */
    class WithinOp(private val expr1: Expression<*>, private val box:  
     PGbox2d) : Op<Boolean>() {
       override fun toSQL(queryBuilder: QueryBuilder) =
        "${expr1.toSQL(queryBuilder)} && ST_MakeEnvelope(${box.llb.x},    
        ${box.llb.y}, ${box.urt.x}, ${box.urt.y}, 4326)"
     }

And an Extension function to use the feature to check if the message is in the specified region:

    /**
      * To check if the message location is within the specified box   
       area.
      * Returns true if yes else false
      */
    infix fun ExpressionWithColumnType<*>.within(box: PGbox2d):  
     Op<Boolean>= WithinOp(this, box)