Category Archives: WEB Programming

Postgres Query with GPS distance calculations without PostGIS

Let’s consider the following scenario:
You have a Postgres Database and an addresses table which stores items with GPS latitude and longitude values. You want to select all the items located inside a circular area around a GPS point you have.

You can write your SQL query without using PostGIS extension.
Let’s define the following data:

1. table: streets
id | name | latitude | longitude

2. GPS_CENTER_LATITUDE, GPS_CENTER_LONGITUDE = coordinates of the center area point around which you wanna search streets

3. AREARADIUS = the area’s radius expressed in meters

select 
	count(*) 
from 
	(
		select 
			(acos(sin(radians(s.latitude)) * sin(radians(GPS_CENTER_LATITUDE)) + cos(radians(s.latitude)) * cos(radians(GPS_CENTER_LATITUDE)) * cos(radians(s.longitude-GPS_CENTER_LONGITUDE))) * 6371 * 1000) computedDistance, 
			s.name
		from 
			streets s 
	) 
as tempQuery 
where 
	computedDistance < AREARADIUS;

WordPress search and replace serialized strings in database

Every now and then I take little projects which involve developing a website in WordPress.
Naturally I install it on a test development machine, do all the stuff required and then move the thing to live server.

The moving part is sometimes painful especially if the site was populated with real data (articles) during testing phase by the client and he doesn’t want to loose the data already entered.

Since WP serializes it’s settings and then saves them into database, a quick search and replace for URLs and other variables which usually change when the site is moved onto other server and domain it’s not productive.

There are some WordPress plugins which help do the search and replace part, but I usually don’t like relying on plugins. And if the installation is dead, I cannot access the plugin anyway, so it’s pointless.

I found a little piece of software which does provide a tremendous help when moving a wordpress site to another hosting or domain and which does not rely on plugins.

The best thing it can do is it can search and replace a string in the entire database, unserializing strings when it has to and serializing them back.

It’s a PHP script you just drop into a secretly named folder on the same level with /wp-admin and the rest and then you navigate with the browser to that folder’s path and the script will try to automatically connect to WordPress’ database and initialize.

You can find the script here: https://interconnectit.com/products/search-and-replace-for-wordpress-databases/, many thanks to it’s creators! 🙂