\\c testCREATE EXTENSION postgis;CREATE EXTENSION postgis_topology;
TABLE
.CREATE TABLE t_user(uid int PRIMARY KEY,name varchar(20),location geometry);CREATE INDEX t_user_location on t_user USING GIST(location);
## Create an automatic name generation functioncreate or replace function random_string(length integer) returns text as$$declarechars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';result text := '';i integer := 0;length2 integer := (select trunc(random() * length + 1));beginif length2 < 0 thenraise exception 'Given length cannot be less than 0';end if;for i in 1..length2 loopresult := result || chars[1+random()*(array_length(chars, 1)-1)];end loop;return result;end;$$ language plpgsql;## Insert ten million rows of test datainsert into t_user select generate_series(1,10000000), random_string(20),st_setsrid(st_makepoint(150-random()*100, 90-random()*100), 4326);
select uid, name, ST_AsText(location), ST_Distance(ST_GeomFromText('POINT(116.404177 39.909652)',4326), location) from t_user order by location <-> 'SRID=4326;POINT(116.404177 39.909652)'::geometry limit 5;
select uid, name, ST_AsText(location),ST_Distance(ST_GeomFromText('POINT(116.404177 39.909652)',4326), location) from t_user where ST_DWithin(location::geography, ST_GeographyFromText('POINT(116.404177 39.909652)'), 1000.0);
Was this page helpful?