GEO_LOCATION and GEO_AREA

geo_area is a foreign key to TR_GEO_AREA, and is traditionally maintained by LP_UPDATE_GEOAREA, which is called by a trigger on the T_ADDRESS table. Meaning, you can customize TR_GEO_AREA and LP_UPDATE_GEOAREA to better fit your organization's needs than the out-of-box functionality, and then use geo_area in list criteria.

geo_location has a data type of geography, which is a "spatial data type" in SQL Server. According to the Table Structures document, this field is available purely for custom use. You can use a geography datatype to store geographic coordinates, so one way to leverage this would be to store the latitude and longitude of each address. We do this using a coarse-grained zip code coordinate data set that is publicly available, and then have a list criteria element that pulls addresses based on "mile radius from theater". Others have gotten fancier with this by queueing a public API for a mapping service to get much finer coordinates for each address — The Arsht Center presented a geocoder at TLCC 2019 that you can read about in the conference archive.

geo_area is a foreign key to TR_GEO_AREA, and is traditionally maintained by LP_UPDATE_GEOAREA, which is called by a trigger on the T_ADDRESS table. Meaning, you can customize TR_GEO_AREA and LP_UPDATE_GEOAREA to better fit your organization's needs than the out-of-box functionality, and then use geo_area in list criteria.

geo_location has a data type of geography, which is a "spatial data type" in SQL Server. According to the Table Structures document, this field is available purely for custom use. You can use a geography datatype to store geographic coordinates, so one way to leverage this would be to store the latitude and longitude of each address. We do this using a coarse-grained zip code coordinate data set that is publicly available, and then have a list criteria element that pulls addresses based on "mile radius from theater". Others have gotten fancier with this by queueing a public API for a mapping service to get much finer coordinates for each address — The Arsht Center presented a geocoder at TLCC 2019 that you can read about in the conference archive.

How do I define our "core market"?

I ran the query:

select *
from TR_GEO_AREA

and got these results:

id  description
1   Manhattan
2   NY City Metropolitan Area
3   Boston/Washington
4   Foreign
5   Other USA addresses
0   Bad USA Zip
6   USA No Zip
7   Core Market
8   Inner Regional Market
9   Outer Regional Market

I know that my coworkers use that 'core market" criteria in extractions often, but I do not know what my predecessor set as "core market."

Any idea what table holds that information?

Thanks,

Ashley

Oh, whoops — misread what you were saying. You'll need to look at the code for LP_UPDATE_GEOAREA to see how it decides to apply the Core Market ID #7. In SSMS, find the stored procedure in the object explorer, then right-click and choose "modify".

Thank you!

Ashley

I found the answer to my question in TR_CITYSTATE and I figured I would update this in case it helps someone else in the future. There is field in TR_CITYSTATE  called geo_area. The number in that field corresponds to the id's in TR_GEO_AREA.