How to query pages based on Zip Code data? | Community
Skip to main content
dnelson42
dnelson42
Level 2
October 16, 2015
Solved

How to query pages based on Zip Code data?

  • October 16, 2015
  • 7 replies
  • 2673 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by JustinEd3

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).

7 replies

J
Adobe Employee
JustinEd3Adobe Employee
Adobe Employee
October 16, 2015

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

smacdonald2008
smacdonald2008
Level 10
October 16, 2015

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. 

A
Abhinav_Sap
Level 2
October 16, 2015

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. 

dnelson42
dnelson42Author
Level 2
October 16, 2015

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.

J
Adobe Employee
JustinEd3Adobe Employee
Adobe Employee
October 16, 2015

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.

dnelson42
dnelson42Author
Level 2
October 16, 2015

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.

J
Adobe Employee
JustinEd3Adobe EmployeeAccepted solution
Adobe Employee
October 16, 2015

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).

Terms & ConditionsAccessibility statement

Loading footer...