It's fuuny, a client has a web site with many parts you wouldn't expect. It's on a Linus and Apache web server running PHP code. Lots of AJAX. It's a super heavy hitter as fas as the database side goes, and they're paying us to - Upgrade them from MS SQL Server 2005 to 2008.
- Implement geography data and queries.
Part 1 is easy.
Now, I'm writing this because I think people will get a kick out of the idea of PHP running against a Darth Vader software product that's trying to kill its masters. Er, I mean a Microsoft product. But also because the capabilities are fascinating.
They've been using a 3rd server to hit an outside web service to process their geography stuff for them, with massive front line caching. They try to get the user's location from the IP address, show them some other location choices in the web front end, and build a polygon. Then they query locations that are within that polygon. Now all of this can be done through a SQL query.
This is one example.
PHP Code:
DECLARE @clickedPoint dbo.Geometry;
SET @clickedPoint = dbo.Geometry::STPoint(@lat, @lon, 0);
SELECT c.id,
c.shape,
c.pop as [Population],
c.shape.STArea() as [Area],
(select count(*) from dbo.business b where c.shape.STIntersects(b.shape)=1
AND substring(sic,1,2)='58') as [Restaurant Count]
FROM dbo.census c, dbo.zipcodes z
WHERE c.shape.STIntersects(z.shape)=1 and z.shape.STIntersects(@clickedPoint)=1;
I've never worked with or even cared that much about geography - it's always just been there, and the math involved is boring and thorough. It's nice to be able to jump into the pool, tho. |
