Expand my Community achievements bar.

SOLVED

How to query pages based on Zip Code data?

Avatar

Level 2

I have been given a task to move a legacy web page that provides a location lookup based on a US zip code into our new AEM 5.6.1 website. The old site has two database tables, a zip code table (zip code, lat, long) and a shop location table that, when combined with a mathematical algorithm, are used to return a list of locations within a 50 miles radius of any given US zip code.

So I have a few questions:
 

  1. How would I import a source table of zip code data into CQ5 (either tabular data or xml)?
  2. How would I use Query API (and/or SQL2) to implement the mathematical radius algorithm?
  3. Is there some other better way to accomplish this in CQ5?

I'm just looking to be pointed in the right direction.

1 Accepted Solution

Avatar

Correct answer by
Employee

As I said, if you want to do geospatial searches, you should be using a query engine which supports geospatial indexes. If you want to look up coverage areas by pre-defined zip codes, you can do that easily with JCR search, but for a variable distance search, use Solr or MongoDB (or something else which has native geospatial capabilities).

View solution in original post

7 Replies

Avatar

Employee

Hi,

The Jackrabbit/CRX2 query engine is not well suited to these kinds of queries. The Oak query engine will be slightly better in the future. But really if you need geospatial search, you should use an engine designed for this purpose, for example Solr or MongoDB have fine geospatial features.

Regards,

Justin

Avatar

Level 10

Another option that you have when working with CQ is to use the DataSourcePool and keep the data in a relational database where you can use SQL. 

Avatar

Level 2

I am assuming you are using something like this to calculate the distance - 

http://en.wikipedia.org/wiki/Haversine_formula

I don't think JCR has support  for trignometric functions to do radius search, if thats what u want to do. 

Avatar

Level 2

It looks like the JCR does not have trig. functions. With that in mind what would be the easiest solution to query a list of "coverage areas" based on zip code? Each coverage area covers some geographic area (not a just a single address). What I need is the ability for users to enter a US zip code and be given a list of "coverage areas" within 50 miles of their zip code.

Avatar

Employee

Each coverage area could be a node (although you would probably want to structure this is some way to avoid a flat node list) with a multivalued property containing the list of zipcodes. At that point, the query becomes very simple.

Avatar

Level 2

The problem with this is each coverage area may cover multiple zip codes and even then I need the search to be based on geographical distance. Without the trig. functions I cant think of a good way to do that radius search in the JCR.

Avatar

Correct answer by
Employee

As I said, if you want to do geospatial searches, you should be using a query engine which supports geospatial indexes. If you want to look up coverage areas by pre-defined zip codes, you can do that easily with JCR search, but for a variable distance search, use Solr or MongoDB (or something else which has native geospatial capabilities).