1

Teradata Query

select F.farm_id, F.name, CASE WHEN geography.ST_GeometryType() = 'ST_POINT' THEN geography.ST_X() ELSE geography.ST_Centroid().ST_X() END as LNG, CASE WHEN geography.ST_GeometryType() = 'ST_POINT' THEN geography.ST_Y() ELSE geography.ST_Centroid().ST_Y() END as LAT from V_farm F, V_farm_workforce FW where F.farm_id=FW.farm_id and FW.account_id='bf064695-b9e3-46a2-a665-0bb794073ae3' and geography is not null

While running above query in Teradata getting below reponse

FarmId                                           Name                  LNG                        LAT
-----------------------------------------------------------------------------------------------------------
ea66e27f-eba4-4750-bd85-2a1e96941b10             Farm099               -88.9495943               33.5847365
2be7e35e-74a7-49eb-9e9d-5b65dbbf7c5e             VFarmJuly23           -106.6409198              31.8018379
56982275-bcad-40f7-83db-5dbfd9f9c82c             Farm112               -88.95096260000003        33.601958

Same query if i run in H2 getting below error

Function "ST_GEOMETRYTYPE" not found; SQL statement:

Can you please suggest how to do in H2 database.

Rob Paller
  • 7,736
  • 29
  • 26
Sriharitha vusirikala
  • 21
  • 4

1 Answers1

1

H2 database can only store and query Geometry type.

Spatial functions in H2 database are available in the H2GIS library. This library is a spatial extension of H2 database. It provides all OGC's simple feature for SQL standards.

The same result should be in this query

select F.farm_id, F.name, ST_X(ST_CENTROID(THE_GEOM)) as LNG,
 ST_Y(ST_CENTROID(THE_GEOM)) as LAT 
 from V_farm F, V_farm_workforce FW 
 where F.farm_id=FW.farm_id 
  and FW.account_id='bf064695-b9e3-46a2-a665-0bb794073ae3'
  and the_geom is not null

ST_CENTROID of a point return the point itself.

nicolas-f
  • 539
  • 7
  • 17