How to create a map of where my patients are from

[brentwood]

I would like to come up with a map to see where my patients are from. It would be a map similar to those marked with houses for sale. In such a way, I have a very clear picture about where my current patients live and which areas I need to spend my marketing dollars. Is there a way to do that without entering addresses one by one? I checked Google Map. I cannot digure out a way to do that. Thanks.

[spike]

This might not help, but you could run an SQL statement based off of the zip codes in the "patient" table. Something like: "SELECT zip, COUNT(*) FROM patient GROUP BY zip ORDER BY zip". It is not the same as having a detailed map, but it would quickly tell you which zip code to spend your marketing dollars.

[brentwood]

Thanks for the reponse. Our patients come from only a handful of zip codes. I was looking for detailed information even within the same zip code. I will run the query you suggested. Thanks.

[nathansparks]

Yeah, I researched this so we could show Open Dental users on a map, it is expensive to purchase a control that does this programatically. If you find something that just needs an input list (like somone could have written an Excel addon that does this) we can help you query the database to get the specified address list out, which would then be used as an input to some third party address mapping software (a virtual pins on a map software). Postback if you find one!

[brentwood]

I found a site to do just that ! Check out http://www.batchgeocode.com/.

The input to the site is rows of addresses (step 2). For me I only use the first few columns: Address City State Zipcode Name. Remove the column headings on the first row for the columns you are not going to use. You can export the patient list from Open Dental to excel. Then save it as a tab-delimited list, and copy it to the text input area of step 2. Again, remember to keep the first row there.

Click "Run Geocoder" in step 5. You will get the maps with pins right below!!!!

You can then save the map as a web page on their site or save the data in KML and then import it into Google Map to create your own Google map. Rmember to save the map as "unlisted" in your Google map. Otherwise, you are going to get into trouble due to privacy issues.

You can also get fancy by adding images or other information.

I do have a concern about having paitent names in the map. No matter whether it is batchgeocode or Google map, it is a public url. I think it is better not using patient names in the map. After I am sure I will not get into trouble, I will add patient names or even phone numbers to it.

SELECT concat(address, ' ', address2) as address, City, State, Zip, concat(LNAME, ', ', FNAME) as name
FROM patient
WHERE PatStatus=0
/*only patients with procedures within the last three years*/
AND EXISTS(SELECT * FROM procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcDate > CURDATE() - INTERVAL 3 YEAR)
GROUP BY Guarantor
ORDER BY LName,FName