How to query pages based on Zip Code data?

[dnelson42]

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.

[justin_at_adobe]

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]

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. 

[Abhinav_Sap]

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]

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.

[justin_at_adobe]

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]

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.

[justin_at_adobe · Accepted solution]

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