New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL/MM Spatial support #631

Closed
timowest opened this Issue Jan 16, 2014 · 20 comments

Comments

Projects
None yet
2 participants
@timowest
Member

timowest commented Jan 16, 2014

Support for SQL/MM spatial types and functions should be added.

Initally ST_Point types and related functions will be supported.

Related supporting libraries

Geometry APIs

SQL/MM and SFS support

Standards

Tasks

  • Basic metamodel for geometry types
  • Operation patterns in SQL
  • Support for serialization/deserialization to/from JDBC
    • Teradata (works via WKT) OK
    • PostGIS (via PGgeometry types) OK
    • MySQL (via custom WKB) OK
    • H2 (via GeoDB) OK
    • Oracle Spatial/Locator MISSING
    • SQL Server (via binary data) OK
  • Support for querydsl-spatial types in querydsl-sql-codegen
    • typename based type detection
    • extensions to MetaDataSerializer
  • querydsl-spatial as an optional dependency of querydsl-sql
  • geometry literal serialization
  • Tests for other types (id 1-5 point etc)
  • Documentation
@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 16, 2014

Member

Related ticket with a similar scope #133

Member

timowest commented Jan 16, 2014

Related ticket with a similar scope #133

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 17, 2014

Member

Geospatial support in Querydsl Mongodb should be synced with this effort, if possible.

Member

timowest commented Jan 17, 2014

Geospatial support in Querydsl Mongodb should be synced with this effort, if possible.

timowest added a commit that referenced this issue Jan 20, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 20, 2014

Member

Challenges/Questions

  • keeping the Querydsl spatial metatypes independent of a GEO API is challenging, might lead to generics explosion in spatial types
  • what GEO API to promote?
  • spatial types and operators in querydsl-core or alternatively as querydsl-spatial?
Member

timowest commented Jan 20, 2014

Challenges/Questions

  • keeping the Querydsl spatial metatypes independent of a GEO API is challenging, might lead to generics explosion in spatial types
  • what GEO API to promote?
  • spatial types and operators in querydsl-core or alternatively as querydsl-spatial?

timowest added a commit that referenced this issue Jan 21, 2014

timowest added a commit that referenced this issue Jan 21, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 27, 2014

Member

what GEO API to promote?

Start with JTS

spatial types and operators in querydsl-core or alternatively as querydsl-spatial?

querydsl-spatial, since it is an extension to the core model

Member

timowest commented Jan 27, 2014

what GEO API to promote?

Start with JTS

spatial types and operators in querydsl-core or alternatively as querydsl-spatial?

querydsl-spatial, since it is an extension to the core model

timowest added a commit that referenced this issue Jan 29, 2014

Use geolatte as GEO API #631
Add tests for teradata and postgresql

timowest added a commit that referenced this issue Feb 3, 2014

timowest added a commit that referenced this issue Feb 3, 2014

timowest added a commit that referenced this issue Feb 4, 2014

timowest added a commit that referenced this issue Feb 4, 2014

timowest added a commit that referenced this issue Feb 4, 2014

timowest added a commit that referenced this issue Feb 5, 2014

timowest added a commit that referenced this issue Feb 6, 2014

timowest added a commit that referenced this issue Feb 6, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 10, 2014

Member

JDBC types in different dialects:

h2       = 2004 BLOB
mysql    = -2   GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION
postgres = 1111 geometry
teradata = 1111 SYSUDTLIB.ST_GEOMETRY
Member

timowest commented Feb 10, 2014

JDBC types in different dialects:

h2       = 2004 BLOB
mysql    = -2   GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION
postgres = 1111 geometry
teradata = 1111 SYSUDTLIB.ST_GEOMETRY

timowest added a commit that referenced this issue Feb 11, 2014

timowest added a commit that referenced this issue Feb 11, 2014

timowest added a commit that referenced this issue Feb 15, 2014

timowest added a commit that referenced this issue Feb 16, 2014

timowest added a commit that referenced this issue Feb 16, 2014

timowest added a commit that referenced this issue Feb 16, 2014

timowest added a commit that referenced this issue Feb 17, 2014

timowest added a commit that referenced this issue Feb 19, 2014

timowest added a commit that referenced this issue Feb 19, 2014

timowest added a commit that referenced this issue Feb 20, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 22, 2014

Member

Oracle Spatial support notes

  • Oracle Spatial is available as an add-on module for Oracle Enterprise
  • Oracle Locator is available for Oracle Standard
  • For JDBC mapping JGeometry (sdoapi) and oracle structs can be used

Docs

Member

timowest commented Feb 22, 2014

Oracle Spatial support notes

  • Oracle Spatial is available as an add-on module for Oracle Enterprise
  • Oracle Locator is available for Oracle Standard
  • For JDBC mapping JGeometry (sdoapi) and oracle structs can be used

Docs

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 22, 2014

Member

Documentation notes

  • section in Querydsl SQL documentation
  • code generation option to enable spatial
  • spatial enabled SQLTemplates subclasses
  • query examples
Member

timowest commented Feb 22, 2014

Documentation notes

  • section in Querydsl SQL documentation
  • code generation option to enable spatial
  • spatial enabled SQLTemplates subclasses
  • query examples

timowest added a commit that referenced this issue Feb 22, 2014

timowest added a commit that referenced this issue Feb 22, 2014

timowest added a commit that referenced this issue Feb 23, 2014

timowest added a commit that referenced this issue Mar 5, 2014

timowest added a commit that referenced this issue Mar 5, 2014

timowest added a commit that referenced this issue Mar 5, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Mar 6, 2014

Member

Different distance types

PostGIS

ST_Distance(geo1, geo2) — Returns the 2-dimensional cartesian minimum distance between two geometries in projected units.

ST_Distance_Sphere(geo1, geo2) — Returns linear distance in meters between two lon/lat points. Uses a spherical earth and radius of 6370986 meters. Faster than ST_Distance_Spheroid, but less accurate. Only implemented for points.

ST_Distance_Spheroid(geo1, geo2, spheroid) — Returns linear distance between two lon/lat points given a particular spheroid. Currently only implemented for points.

Teradata

http://developer.teradata.com/extensibility/articles/quicker-method-to-calculate-distances-on-the-globe

SQL Server

TODO

MySQL

TODO

GeoDB

TODO

related http://suite.opengeo.org/opengeo-docs/dataadmin/pgBasics/geography.html

Member

timowest commented Mar 6, 2014

Different distance types

PostGIS

ST_Distance(geo1, geo2) — Returns the 2-dimensional cartesian minimum distance between two geometries in projected units.

ST_Distance_Sphere(geo1, geo2) — Returns linear distance in meters between two lon/lat points. Uses a spherical earth and radius of 6370986 meters. Faster than ST_Distance_Spheroid, but less accurate. Only implemented for points.

ST_Distance_Spheroid(geo1, geo2, spheroid) — Returns linear distance between two lon/lat points given a particular spheroid. Currently only implemented for points.

Teradata

http://developer.teradata.com/extensibility/articles/quicker-method-to-calculate-distances-on-the-globe

SQL Server

TODO

MySQL

TODO

GeoDB

TODO

related http://suite.opengeo.org/opengeo-docs/dataadmin/pgBasics/geography.html

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Mar 8, 2014

Member

Access to SPATIAL_REF_SYS

PostGIS: public.spatial_ref_sys
Teradata: sysspatial.spatial_ref_sys, sysspatial.geometry_columns
SQLServer: ?

Member

timowest commented Mar 8, 2014

Access to SPATIAL_REF_SYS

PostGIS: public.spatial_ref_sys
Teradata: sysspatial.spatial_ref_sys, sysspatial.geometry_columns
SQLServer: ?

timowest added a commit that referenced this issue Mar 8, 2014

timowest added a commit that referenced this issue Mar 14, 2014

timowest added a commit that referenced this issue Mar 14, 2014

timowest added a commit that referenced this issue Mar 14, 2014

@beloglazov

This comment has been minimized.

Show comment
Hide comment
@beloglazov

beloglazov Mar 19, 2014

Hi Timo,

First of all, thanks for your great work! I'm trying to use this spatial extension with JPA but not getting the spatial methods on geometry fields. Is this extension already supported by apt-maven-plugin? Here is my maven configuration of the plugin (I've added the spatial option):

            <plugin>                                                             
                <groupId>com.mysema.maven</groupId>                              
                <artifactId>apt-maven-plugin</artifactId>                        
                <version>1.0.9</version>                                         
                <executions>                                                     
                    <execution>                                                  
                        <goals>                                                  
                            <goal>process</goal>                                 
                        </goals>                                                 
                        <configuration>                                          
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
                            <spatial>true</spatial>                              
                        </configuration>                                         
                    </execution>                                                 
                </executions>                                                    
            </plugin>

Hi Timo,

First of all, thanks for your great work! I'm trying to use this spatial extension with JPA but not getting the spatial methods on geometry fields. Is this extension already supported by apt-maven-plugin? Here is my maven configuration of the plugin (I've added the spatial option):

            <plugin>                                                             
                <groupId>com.mysema.maven</groupId>                              
                <artifactId>apt-maven-plugin</artifactId>                        
                <version>1.0.9</version>                                         
                <executions>                                                     
                    <execution>                                                  
                        <goals>                                                  
                            <goal>process</goal>                                 
                        </goals>                                                 
                        <configuration>                                          
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
                            <spatial>true</spatial>                              
                        </configuration>                                         
                    </execution>                                                 
                </executions>                                                    
            </plugin>
@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Mar 19, 2014

Member

@beloglazov For now the spatial extensions are only supported in the SQL module. For Hibernate or JPA it might be possible to integrate this with Hibernate Spatial in the future.

Member

timowest commented Mar 19, 2014

@beloglazov For now the spatial extensions are only supported in the SQL module. For Hibernate or JPA it might be possible to integrate this with Hibernate Spatial in the future.

@beloglazov

This comment has been minimized.

Show comment
Hide comment
@beloglazov

beloglazov Mar 19, 2014

@timowest thanks for your reply, I'm looking forward to that!

@timowest thanks for your reply, I'm looking forward to that!

@beloglazov

This comment has been minimized.

Show comment
Hide comment
@beloglazov

beloglazov Mar 24, 2014

@timowest could you please point me into a direction of how to add support for a custom geospatial function when using the SQL module? For example, currently ST_Transform is not supported and I would like to add it. Thanks!

@timowest could you please point me into a direction of how to add support for a custom geospatial function when using the SQL module? For example, currently ST_Transform is not supported and I would like to add it. Thanks!

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Mar 24, 2014

Member

@beloglazov Preferably list the missing functions here and I can add them. Since the spatial support has not yet been merged to the master branch additions can be done without much overhead.

Member

timowest commented Mar 24, 2014

@beloglazov Preferably list the missing functions here and I can add them. Since the spatial support has not yet been merged to the master branch additions can be done without much overhead.

timowest added a commit that referenced this issue Mar 24, 2014

timowest added a commit that referenced this issue Mar 24, 2014

@beloglazov

This comment has been minimized.

Show comment
Hide comment
@beloglazov

beloglazov Mar 26, 2014

@timowest sorry for taking long to reply. Here is a list of functions that we are currently using in PostGIS that I couldn't find in Querydsl:

ST_AsEWKT
ST_GeomFromText
ST_SetSRID
ST_XMin
ST_XMax
ST_YMin
ST_YMax
ST_DWithin
ST_Extent
ST_Collect
ST_Dump
ST_Translate

There are also some geometry construction functions:

ST_Point
ST_MakePoint
ST_MakeLine
ST_MakeBox2D

PostGIS also supports a type called Geography (http://postgis.refractions.net/docs/using_postgis_dbmanagement.html#PostGIS_Geography). I'm not sure if you are planning to support this type. Thanks for your time and great work!

@timowest sorry for taking long to reply. Here is a list of functions that we are currently using in PostGIS that I couldn't find in Querydsl:

ST_AsEWKT
ST_GeomFromText
ST_SetSRID
ST_XMin
ST_XMax
ST_YMin
ST_YMax
ST_DWithin
ST_Extent
ST_Collect
ST_Dump
ST_Translate

There are also some geometry construction functions:

ST_Point
ST_MakePoint
ST_MakeLine
ST_MakeBox2D

PostGIS also supports a type called Geography (http://postgis.refractions.net/docs/using_postgis_dbmanagement.html#PostGIS_Geography). I'm not sure if you are planning to support this type. Thanks for your time and great work!

@timowest timowest removed the teradata label Mar 28, 2014

@timowest timowest added this to the 3.4.0 milestone Apr 30, 2014

@timowest timowest added the fixed label May 22, 2014

@timowest timowest removed the progress label Jun 8, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 8, 2014

Member

@beloglazov I moved the spatial additions here #792

Member

timowest commented Jun 8, 2014

@beloglazov I moved the spatial additions here #792

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 9, 2014

Member

Released in 3.4.0

Member

timowest commented Jun 9, 2014

Released in 3.4.0

@timowest timowest closed this Jun 9, 2014

@beloglazov

This comment has been minimized.

Show comment
Hide comment
@beloglazov

beloglazov Jun 10, 2014

We've switched to 3.4.0, tests pass, thanks!

We've switched to 3.4.0, tests pass, thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment