I am relatively new to using geo functions but I want to make sure my places dimension table does not have any duplicates by doing a delta operation from the freshly geocoded records in my staging table. First, I attempted to do a left outer join on the point_geo column:
select loc_id, point_geo from st.stg_location_dim as a left outer join dw.dim_location as b
on a.point_geo = b.point_geo
order by loc_id desc
so then I tried to use st_within (I am not even sure if you can use st within for points although it makes sense logically to me since one point would fully overlap the other.
select loc_id, point_geo from st.stg_location_dim as a, dw.dim_location as b
where a.point_geo.st_within(b.point_geo) = 1
order by loc_id desc
select loc_id, point_geo from st.stg_location_dim as a left outer join dw.dim_location as b
on a.point_geo.st_asbinary() = b.point_geo.st_asbinary()
order by loc_id desc
1) What geo function can I use to determine if my staging table contains records that already exist in my dimension table.
2) Is there any way to use st_geometry as either a usi or upi?
Fun facts:
DBS v12.00.03.33
Geospatial Extension v1.5
Point geo in the examples above is defined as POINT_GEO SYSUDTLIB.ST_GEOMETRY NOT NULL,
I can provide full table ddl if it helps.
Both of these tables include three records each with one record having a common point_geo.
I am relatively new to using geo functions but I want to make sure my places dimension table does not have any duplicates by doing a delta operation from the freshly geocoded records in my staging table. First, I attempted to do a left outer join on the point_geo column:
ERROR: [Teradata][ODBC Teradata Driver][Teradata Database] Row size or Sort Key size overflow. Error Code: -3577
so then I tried to use st_within (I am not even sure if you can use st within for points although it makes sense logically to me since one point would fully overlap the other.
ERROR: [Teradata][ODBC Teradata Driver][Teradata Database] Row size or Sort Key size overflow. Error Code: -3577
Then I thought maybe I need to use st_asbinary
ERROR: [Teradata][ODBC Teradata Driver][Teradata Database] Row size or Sort Key size overflow. Error Code: -3577
A couple questions-
1) What geo function can I use to determine if my staging table contains records that already exist in my dimension table.
2) Is there any way to use st_geometry as either a usi or upi?
Fun facts:
DBS v12.00.03.33
Geospatial Extension v1.5
Point geo in the examples above is defined as POINT_GEO SYSUDTLIB.ST_GEOMETRY NOT NULL,
I can provide full table ddl if it helps.
Both of these tables include three records each with one record having a common point_geo.
Thanks in advance!