Support » Plugin: WP-GeoMeta » Query example for nearby points within a raduis

  • Hi
    Can you give an example on how to find nearby points within 1km radius ?
    Does your plugin use extra tables other than wp meta ?
    Any benchmarks for millions of points ?

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author Michael Moore

    (@stuporglue)

    Can you give an example on how to find nearby points within 1km radius ?

    WP-GeoMeta uses MySQL’s built-in spatial functions, which doesn’t include ST_DWithin at this time.

    So finding points within a radius is a two step process.

    1) First, we buffer the center point by 1km to get a polygon representing our area of interest.
    2) Second, we intersect our buffer with the points data.

    Now, with code:

    
    /* Get the center point at GeoJSON. If you're using WP-GeoMeta, 
    * you're probably storing your geometry as GeoJSON anyways. 
    * lat/lng is also supported, but then you'll need to construct 
    * GeoJSON manually
    */
    $center_point = get_post_meta(15, 'geom'); 
    
    /*
    * MySQL supports ST_Buffer, but the buffer units must be the same as the 
    * units for your projection. WP-GeoMeta includes custom SQL functions
    * for buffering in meters or miles: https://github.com/BrilliantPlugins/wp-geometa-lib/blob/master/sql/buffer_point.sql
    *
    * Buffer the center point by 1000 meters, using 8 points per quarter circle in 
    * the output polygon. Note: This follows the same syntax as PostGIS's ST_Buffer
    */
    $buffered_shape = WP_GeoUtil::wp_buffer_point_m($center_point,1000,8);
    
    /*
    * Now run WP_Query with two meta_query params. inbuffer filters for posts within our buffer. distance is calculated so taht we can order by distance from $center_point
    */
    $q = new WP_Query( array(
    	'orderby' => 'distance',
    	'order' => 'ASC',
    	'meta_query' => array(
    		'inbuffer' => array(
    			'key' => 'geom',
    			'compare' => 'ST_INTERSECTS',
    			'value' => $buffered_shape
    		),
    		'distance' => array(
    			'key' => 'geom',
    			'compare' => 'ST_DISTANCE',
    			'value' => $center_point,
    			'type' => 'DECIMAL(10,7)'
    		)
    	)
    ));
    
    while($q->have_posts() ) {
    	$q->the_post();
    	print "\t* " . get_the_title() . "\n";
    }
    
    Plugin Author Michael Moore

    (@stuporglue)

    Does your plugin use extra tables other than wp meta ?

    Yes, but it should be transparent and all original data is still stored in the regular meta tables.

    WP-GeoMeta creates wp_postmeta_geo, wp_usermeta_geo, wp_termmeta_geo and wp_commentmeta_geo. These tables have a meta value column of type GEOMETRYCOLLECTION.

    When you or a plugin save postmeta (or other types of meta), WP-GeoMeta checks the meta value is GeoJSON, and if so, saves a copy of the data in MySQL’s geometry format.

    Later when you query postmeta (or another type of meta), WP-GeoMeta detects if you’re using a spatial function, and if so, it alters the query to fetch that data from the appropriate *meta_geo table.

    Because the original/source data is still stored in postmeta, WP-GeoMeta shouldn’t affect any other plugins or where data is stored. It also means that if something goes wrong, the *meta_geo tables can be rebuilt from the original data in the postmeta table.

    Any benchmarks for millions of points ?

    I’m afraid not. I suspect that querying for points within 1km of a central point will be faster with WP-GeoMeta than with a custom haversine formula on the postmeta table.

    WP-GeoMeta takes advantage of MySQL’s spatial indexes, which should make the ST_INTERSECTS query much faster than alternatives. However, I haven’t run any benchmarks so I can’t be sure.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Query example for nearby points within a raduis’ is closed to new replies.