postgis命令操作

postgis操作：
1. 连接数据库
psql -hlocalhost -p5432 -Upostgres -d postgis_30_sample
2. 列出所有表
\d
3. 执行查询
select st_astext(geom) from "public"."gadm36_usa_0";

4. 导出查询结果
\o d:/sql.sql
select st_astext(geom) from "public"."gadm36_usa_0";

select st_srid(geom),* from gadm36_chn_0;

#创建存储多面的表
create table tbl (area_code varchar, multi_polygon geometry);
#插入数据
INSERT INTO public.tbl(multi_polygon, area_code)
VALUES ('MULTIPOLYGON(((120.910836021004 31.0847688021281, 120.907682968664 31.0904969547615, 120.903682095584 31.0926221351667, 120.898430601572 31.099377105291, 120.894383016605 31.1000450849384, 120.888329695096 31.1010208786428, 120.878374493773 31.1054320386557, 120.869499235335 31.1059197928766, 120.863182081497 31.11214690101, 120.856629930938 31.1162269705629, 120.853383993808 31.1180535458062, 120.852819947778 31.1193522143416, 120.851674967575 31.1208756132075, 120.849377191911 31.1221117964085, 120.841962668062 31.1285774767053, 120.824344471587 31.1347227952236, 120.820955793711 31.1581202873129, 120.830077996335 31.1585873726697, 120.829628124935 31.1651707420789, 120.828852348297 31.172378476022, 120.827545223949 31.1762960411599, 120.813913437835 31.1827403029449, 120.799080707085 31.1923633128056, 120.780398557564 31.2005109744088, 120.782374470363 31.2088321216307, 120.784184825458 31.218414466427, 120.771391338046 31.2258295558585, 120.765068435053 31.2264771043622, 120.760345054851 31.2254873291421, 120.753757870376 31.2167992304556, 120.748105821938 31.2076166932395, 120.733109599585 31.1915894152993, 120.72492774317 31.1983518632801, 120.722203272389 31.2059911556878, 120.700868606357 31.2070255357833, 120.691286381482 31.2019837901547, 120.684639819014 31.1989487126743, 120.682446156958 31.1944953013475, 120.677696636066 31.2002301228905, 120.671365199171 31.205230960883, 120.669692823803 31.221740277755, 120.662490389885 31.2259895906159, 120.659765649612 31.2259942225874, 120.652980837926 31.2285176093958, 120.648671217242 31.2314134722241, 120.641028703454 31.2321154814005, 120.63901622962 31.2246680025729, 120.643063814587 31.2180265838056, 120.638633281981 31.2162964564183, 120.636230224912 31.214831277796, 120.634141395109 31.2120168228335, 120.630458162857 31.2082874547629, 120.63353997957 31.20245243306, 120.630091294887 31.201117076452, 120.627236569807 31.1875004181431, 120.628025461706 31.1815763615033, 120.620647948044 31.1743227575322, 120.616301945987 31.1684945041326, 120.610210087171 31.1673099924041, 120.601042430289 31.1627756631745, 120.587178971184 31.1434562096783, 120.567178558326 31.1103359791139, 120.550631770832 31.1019889320587, 120.468010301213 31.0308393602696, 120.448246142705 31.0088994280804, 120.434218203861 30.9903516329255, 120.380322927501 30.9574101832348, 120.378386899466 30.9523689073463, 120.380032684991 30.950732374344, 120.379014994679 30.9483501102116, 120.377523538041 30.9478873239784, 120.37721200569 30.9465963575439, 120.375869398274 30.9460030939852, 120.374004965189 30.9470133897115, 120.374147166951 30.9447530626375, 120.37232441524 30.9430609566083, 120.371677994595 30.9414703354135, 120.368664988084 30.9408775043183, 120.366519026052 30.9393339269358, 120.367563036715 30.93815628761, 120.367355887465 30.9368354525818, 120.368257247213 30.936428210111, 120.366397125994 30.9329690177344, 120.370310144795 30.9325304726318, 120.372068038848 30.9297043906307, 120.36784249956 30.9267025008949, 120.363015454902 30.9252910485589, 120.36566114429 30.9205542233898, 120.363940619746 30.9183409750334, 120.363569619571 30.9162394410382, 120.361356554116 30.9154440633584, 120.36254725807 30.9101525289147, 120.366184317419 30.9102658512177, 120.36663293119 30.9066867994296, 120.367812136833 30.8986798615466, 120.363953824837 30.8950996749698, 120.367603011311 30.8901559536727, 120.368899625484 30.8868920979622, 120.375152730139 30.8878570029334, 120.378600875839 30.8903178767914, 120.383511552739 30.8912367271472, 120.390069182962 30.8971599992281, 120.398334761451 30.8967518114513, 120.405173471468 30.8976482108826, 120.410396758687 30.8993621222105, 120.410934753856 30.9003173718505, 120.413655182263 30.9017891548008, 120.419622535935 30.9027281123394, 120.41986948012 30.9042060549566, 120.42122915534 30.9035552796673, 120.421152080727 30.9045104095844, 120.421864436996 30.9040929577983, 120.422950488358 30.9041395135627, 120.422661144153 30.9061530091232, 120.428261001041 30.9068906010303, 120.430308239299 30.9082746741933, 120.426161122081 30.9204871513236, 120.423556305594 30.9291233346802, 120.425451370898 30.9333301983311, 120.430130913791 30.9332548492338, 120.433402542458 30.9294136694933, 120.440188611772 30.9297590653107, 120.440724271348 30.9286379182854, 120.443070016526 30.9286539491474, 120.441872575281 30.9258553996798, 120.443962033897 30.9194613920067, 120.440579284838 30.9178656496576, 120.439201913 30.916319141092, 120.441195522418 30.9079338476658, 120.445725138122 30.9053638987175, 120.443695506653 30.8989830718994, 120.44060722214 30.8970178425533, 120.442794955379 30.8919544468975, 120.44627418245 30.8893622164907, 120.452096729274 30.8819172659048, 120.4561230244 30.8787361901656, 120.459052039346 30.8758697601166, 120.457087355365 30.8734895647739, 120.456920001048 30.8721103305983, 120.454504637193 30.87154643432, 120.454334677791 30.8689820084405, 120.449416994107 30.8668793801422, 120.44669809282 30.8651855731228, 120.44768281532 30.8632858260727, 120.449286380486 30.8613972783187, 120.449499458553 30.8588426533272, 120.451321042466 30.8588454433565, 120.452430449771 30.8572076045566, 120.453929003022 30.855777369117, 120.455022689981 30.8557149789264, 120.454873122113 30.8566163388167, 120.455487113929 30.8565579793168, 120.458227035565 30.8557788416801, 120.461155242036 30.8549914822309, 120.462428410435 30.8540145398098, 120.461832654221 30.8503429135422, 120.466164552882 30.8457836431093, 120.466604093768 30.8432382872631, 120.465059996427 30.8425081843327, 120.466765968421 30.8353641345991, 120.467179099125 30.832700278408, 120.465896318857 30.8312050194817, 120.463216493859 30.8301594560903, 120.464626564018 30.8257035696399, 120.465314037225 30.8218360140226, 120.46839612343 30.8214910004157, 120.469939591957 30.8180358333109, 120.472447211788 30.8120829660581, 120.477851866889 30.8116178087079, 120.478883391106 30.8137237538094, 120.482661125098 30.8132494549989, 120.483377433911 30.807443309321, 120.483823712089 30.8061536872621, 120.482830725178 30.8039760000383, 120.482565096239 30.8008845958101, 120.482268385929 30.798298952403, 120.482733079369 30.7958332120776, 120.483292723702 30.7935096421893, 120.481452814356 30.7922106797844, 120.482362438516 30.7916028005496, 120.485832952023 30.7880786066355, 120.486046119921 30.7838738337543, 120.48682369317 30.7823242110475, 120.490154879493 30.7808318056852, 120.493229150439 30.7763690320723, 120.495714043141 30.7699142202206, 120.509757792162 30.7641880516342, 120.526559428929 30.7815630026904, 120.540876891639 30.801570506158, 120.555996451338 30.8230547936402, 120.56490809115 30.8333028561436, 120.568592311538 30.8398172426654, 120.573402018899 30.843064424212, 120.580567801953 30.8475910384381, 120.589708060517 30.8558146482052, 120.595243059747 30.8606815748539, 120.598895300459 30.8588858212713, 120.602805175191 30.85930626207, 120.615766735564 30.8550134158486, 120.62979512356 30.8601082331994, 120.634391393533 30.8623625200876, 120.641889729286 30.860151090569, 120.647837410067 30.8611029376878, 120.651432608379 30.8607573691525, 120.65459392513 30.857277743976, 120.657607290963 30.8535901228187, 120.662258806724 30.8524018846633, 120.662971791808 30.8609639044342, 120.664439263689 30.869267877979, 120.667709365237 30.8717852595648, 120.671145383812 30.8670704807798, 120.674657309204 30.8681471001742, 120.677899114128 30.8741913012611, 120.67968135226 30.8809402906759, 120.687401299982 30.8840282387694, 120.690168080953 30.8908457132485, 120.69139525611 30.8880294668475, 120.692544278688 30.8851890407512, 120.696728675584 30.8809976279091, 120.700983769123 30.876098036286, 120.707172016121 30.8745971840201, 120.706227717369 30.8797947009889, 120.709017584791 30.882770725998, 120.706633032815 30.8891414099442, 120.712928896812 30.8903986058219, 120.719528028749 30.8926658084136, 120.716910456324 30.9083495009441, 120.718261956964 30.9173528346457, 120.718322772247 30.9308973137291, 120.714182661812 30.9438556377289, 120.702233940902 30.9564041792797, 120.689950691019 30.961292418926, 120.692695373675 30.9668670572396, 120.696267665196 30.9685609823416, 120.696870787516 30.9715861601433, 120.703279838007 30.9756244822762, 120.707965219886 30.9732008904435, 120.711897911578 30.9725245029398, 120.714399602593 30.976348915946, 120.71675001896 30.9782443066273, 120.725953069079 30.9779156521527, 120.735179744633 30.9780354726164, 120.742430776879 30.9744464432774, 120.74550576647 30.9724638925621, 120.751782676221 30.9694108751063, 120.75494866416 30.975570608154, 120.759442257813 30.9764483035705, 120.767732270212 30.9813024567895, 120.774912156661 30.9827350550246, 120.774201507173 30.9852253511661, 120.772241135058 30.993509921718, 120.774512051387 30.9986115318759, 120.775303368711 31.0023777025706, 120.779884816642 31.0012854294837, 120.781539315731 31.0039256675227, 120.810845814349 31.0111627865528, 120.830388720211 31.0111649530865, 120.841069572724 31.0077892785352, 120.845175996702 31.0062918288413, 120.847833453892 30.9984522703404, 120.851850945624 30.9985177394006, 120.858159296068 30.9974142004749, 120.864090178537 30.9971985987272, 120.870246445858 30.9960452061423, 120.872087972153 30.9972637591063, 120.879543189242 31.0013036921769, 120.890045638281 31.00656892692, 120.894768838822 31.0026230850305, 120.898926196722 31.009335761948, 120.899995539601 31.011507961166, 120.902543134027 31.018319117643, 120.903086788522 31.0222940896392, 120.908400265614 31.0240241563235, 120.90809636886 31.0320785830338, 120.907211987085 31.0442992057865, 120.903750815955 31.0534771485912, 120.900428882179 31.0642242183186, 120.90492553007 31.078312634774, 120.910041918933 31.0838865474957, 120.910836021004 31.0847688021281)))'
, '0301030405');
INSERT INTO public.tbl(multi_polygon, area_code)
VALUES ('MULTIPOLYGON(((120.908219077392 31.2309982391137, 120.90370104983 31.2415101174496, 120.899113134099 31.2438632794139, 120.894225902972 31.2514579532449, 120.898181860777 31.2563191468406, 120.89198876293 31.2606442142611, 120.889719553381 31.2657802612324, 120.889431107482 31.2681508688591, 120.887830506724 31.2714993721414, 120.884610979776 31.2756024464323, 120.890112831533 31.2764747200046, 120.887352518362 31.280915665971, 120.889636370291 31.2826203720493, 120.887669710038 31.2841550823956, 120.887620393065 31.2863995469956, 120.890621182621 31.2881854671645, 120.886126690663 31.2900473426354, 120.885692359949 31.2920514364212, 120.878919585557 31.2979988933902, 120.875039175245 31.3061878003098, 120.871163436122 31.3135720952614, 120.861910440217 31.3096166978469, 120.857732780612 31.2968544363927, 120.850144254986 31.2882187951698, 120.842640259908 31.2911385754176, 120.837090618298 31.2957530877032, 120.825141358405 31.2977163289103, 120.814122473528 31.2991930139306, 120.809686551089 31.2976080238706, 120.808669130268 31.2972920563128, 120.808345650454 31.294136410489, 120.807296609279 31.2918775514759, 120.808622598043 31.2829012848729, 120.810556110822 31.2736994652094, 120.798170633773 31.2730913641426, 120.796701814434 31.2717196692786, 120.794933230546 31.2723978416875, 120.796126270093 31.270694492265, 120.795079564513 31.2686499689748, 120.792408363249 31.2687164074255, 120.791561620475 31.2705574513421, 120.790121097759 31.2738825660494, 120.781617737802 31.2704734210821, 120.777074108532 31.2518789610692, 120.76733899206 31.245461530976, 120.760468751519 31.2462420840481, 120.754869703106 31.2459902361816, 120.75198632208 31.2450499878622, 120.743219219625 31.2394032049768, 120.732054988916 31.2464367389065, 120.723185389803 31.2549986046999, 120.714437780577 31.2585018476444, 120.712549183073 31.2620514926619, 120.70768216382 31.2659687792514, 120.705738320527 31.2699340514983, 120.703375866866 31.2730682931068, 120.697895933763 31.2764829758943, 120.69208156152 31.2798902812702, 120.688898415945 31.2886164930192, 120.685766743276 31.2957904243849, 120.682239636522 31.2933823321152, 120.682339977247 31.2900909304744, 120.680922630814 31.2857672263218, 120.678160610862 31.2831886004928, 120.677839466642 31.2803366220441, 120.678686209416 31.2775894128178, 120.679911947284 31.2758532114515, 120.6790569401 31.2732825680262, 120.679417969084 31.2710663382159, 120.679443750452 31.2691470601627, 120.679428569089 31.268355123666, 120.678731484014 31.2666094907395, 120.677685946231 31.2661042064105, 120.677571052956 31.2662759786957, 120.676377833747 31.2664621807309, 120.675824926706 31.2666691400379, 120.675343704444 31.2669818931095, 120.675441260422 31.2672496576758, 120.674871105915 31.2675539206305, 120.674651649879 31.26753015374, 120.674489865057 31.2675989079424, 120.67464697869 31.2681829693797, 120.6735317324 31.2685327563084, 120.6731991797 31.2686325298382, 120.673238615312 31.2691676629226, 120.673644739233 31.2703180922208, 120.673585451069 31.2707336137078, 120.672887377857 31.2709249767422, 120.672682654032 31.2709967377791, 120.672494369197 31.2726512387845, 120.672098485959 31.2731019351504, 120.672332584374 31.2737061009815, 120.672306264023 31.2741547861038, 120.672375613208 31.2747820929024, 120.672426008147 31.2753252152751, 120.671322888981 31.2759953370496, 120.670936887104 31.2765754881029, 120.670591847957 31.2769669857931, 120.670673503928 31.2773302423319, 120.668850303065 31.2808747742459, 120.663727536233 31.2800074796852, 120.663264998726 31.2802387898842, 120.662296625386 31.2808643584263, 120.661766355644 31.2818104958798, 120.661896250621 31.2821798286767, 120.661967126925 31.2824204690007, 120.661841813307 31.2826729902256, 120.661685687809 31.2826587941223, 120.660661350036 31.2825061858755, 120.660363022776 31.2839827257748, 120.660326731234 31.2844508807965, 120.659820266587 31.2855059183213, 120.658940106848 31.2847061739681, 120.654410850467 31.2875510755718, 120.656079363121 31.2767133689311, 120.650019933134 31.2783311194363, 120.645116173186 31.2793222642221, 120.639619801092 31.2792068395259, 120.637941137586 31.2798730756973, 120.637153952468 31.2804377717484, 120.636563406425 31.2828514443182, 120.630944146138 31.2831309677044, 120.627818941269 31.2831868259898, 120.623183325515 31.2827003022878, 120.61962828146 31.2823027335429, 120.617427882113 31.2809776207582, 120.619767339153 31.2800824742488, 120.621445912828 31.2775076264367, 120.620450859815 31.2760849177308, 120.617986268818 31.2750160394516, 120.616352879909 31.2730290183204, 120.616096323855 31.274071144879, 120.61524446074 31.2688830049122, 120.616086622156 31.2675929662232, 120.61571229825 31.2670494130653, 120.616714358046 31.2644898710316, 120.616204120517 31.2634109824943, 120.612036611764 31.2630009846068, 120.609636968255 31.2629783740931, 120.60969311235 31.2587442467634, 120.605417806936 31.2588280560547, 120.598941742854 31.2530833957143, 120.602383690246 31.2354115501651, 120.595583068382 31.2292083695569, 120.594330291518 31.2263755874573, 120.590308308258 31.225698084746, 120.584998514219 31.2266443175183, 120.58249933846 31.2360107878315, 120.577444034372 31.2411292773919, 120.571693351989 31.2434106667742, 120.57100219573 31.2482273593561, 120.573905968291 31.2515788147135, 120.575726204745 31.2563573482086, 120.572716701626 31.2587070496137, 120.572675469403 31.2602179210966, 120.568050902807 31.2642047357613, 120.570910388906 31.2686072970994, 120.56795199937 31.2770310264495, 120.559519515718 31.2777923353221, 120.548314142617 31.2739805591876, 120.54261008229 31.2779890077011, 120.538382027746 31.2815492539595, 120.536554065863 31.2865203632695, 120.532235551955 31.2927361727456, 120.530210232352 31.2965926194396, 120.5278883821 31.2984073491317, 120.514253721409 31.2964206714889, 120.508330025384 31.2980328350119, 120.50733811644 31.3039480539846, 120.505127476411 31.2989484827385, 120.502763136308 31.2946288145932, 120.499747704373 31.2963916663301, 120.498360720665 31.2988517500333, 120.496437268907 31.3020457276407, 120.490629005141 31.3023776463475, 120.492974660488 31.3036403613831, 120.490757462829 31.3057244594659, 120.487721010544 31.3028716268241, 120.486240513233 31.3046006921653, 120.48701934411 31.3126267425364, 120.484412551351 31.3171489542279, 120.477074563131 31.3187670065143, 120.472785423813 31.3207655476861, 120.465677401804 31.323299948472, 120.458109537205 31.3226025574695, 120.451970876672 31.3175285589105, 120.451348979767 31.3104116780911, 120.450349345396 31.3045397558069, 120.441696148079 31.3058819663003, 120.435661601155 31.3055503685957, 120.434931638097 31.3097319306802, 120.427296670076 31.3139048984805, 120.426851559696 31.3213304590406, 120.424060883799 31.3264409581773, 120.420588124528 31.3274407687926, 120.418386198062 31.330498665219, 120.40525036642 31.3298279323166, 120.401848842776 31.3299275632163, 120.392068721197 31.3313899351572, 120.37933479144 31.3304197012835, 120.379309099903 31.3258167747902, 120.369354796886 31.3250893267327, 120.360244901048 31.3243847771197, 120.360935069171 31.3195094656274, 120.341007149568 31.3080308821475, 120.298907073707 31.2963275618114, 120.27290068004 31.3004168173499, 120.223982195867 31.3392541476813, 120.061177527274 31.2641444675454, 119.927382916482 31.1768901731467, 119.927710887824 31.1708821759929, 119.927734333598 31.1695309203126, 119.936422924151 31.1580036314937, 119.947659827776 31.1460124939177, 119.949359960784 31.1293604721219, 119.95402854435 31.1182697985878, 119.95359493228 31.1113816943407, 119.965628453231 31.0961044698547, 119.973771233356 31.0839047941918, 119.979542846258 31.0754196988525, 119.989487627237 31.0685162381552, 119.994734360229 31.0574976601454, 120.001474076979 31.0430531047934, 120.005845501082 31.0349275190877, 120.017124086082 31.0282982025654, 120.041491431515 31.0171565438165, 120.068186196694 31.0039433880303, 120.0881000129 30.9892431227398, 120.11378903523 30.9676503910601, 120.114419286376 30.9657241322127, 120.115635412375 30.964225628501, 120.118992739388 30.9621232551878, 120.124991982906 30.958879298307, 120.137957765315 30.9510697175457, 120.1426311099 30.9486070922535, 120.147805439468 30.9476592207125, 120.151992261789 30.9452679747258, 120.160444957316 30.9431802792213, 120.177740302791 30.9404472813078, 120.190938207343 30.9386997318814, 120.199713304718 30.9373125412188, 120.204234296688 30.9351632615425, 120.207456967705 30.9351799882454, 120.211774313817 30.9362134759255, 120.229826841006 30.9337197204576, 120.246390157322 30.9328446486251, 120.260076829902 30.9331531704493, 120.26683909412 30.9334666474175, 120.278216043574 30.9343032262506, 120.282115497962 30.9340794268428, 120.288334377177 30.935035797774, 120.299536157056 30.9363971577039, 120.305995422825 30.93704809456, 120.316914236468 30.9382146743343, 120.328456294475 30.9409280685573, 120.338629784036 30.944085995668, 120.350494064229 30.9466881884018, 120.362513661444 30.9504047856125, 120.374009905869 30.9542736824458, 120.38554136387 30.960817541474, 120.435026948312 30.9916479611556, 120.451299932286 31.0120842552633, 120.498934109072 31.0634326378198, 120.556380117621 31.1040326879, 120.570411649687 31.1137165600043, 120.590903166167 31.1497924743698, 120.604248482654 31.1680137953266, 120.612643057814 31.1676349737315, 120.616656686833 31.169012057059, 120.621581107808 31.1751244500488, 120.627854873489 31.1821642811764, 120.626520260993 31.1888374362017, 120.631570085418 31.2010123660212, 120.632398862081 31.2038889259428, 120.631071256368 31.2095478124763, 120.635174895599 31.2125245613341, 120.636339997845 31.2151153282863, 120.639914624961 31.2165215932836, 120.644162532056 31.2202697800399, 120.63821413263 31.2284725260788, 120.643802670869 31.2324106726017, 120.648906124132 31.2310839259633, 120.654034190967 31.2287119923528, 120.660256843065 31.2249059345742, 120.662717751009 31.2262567777992, 120.669915334078 31.2186463922903, 120.670915956585 31.2024432440059, 120.67840638725 31.1988904101776, 120.683496815083 31.1951935716867, 120.686824048861 31.2003000848817, 120.691727449488 31.2021749850059, 120.704921042174 31.2073663629122, 120.72407031056 31.2056571185884, 120.725339346754 31.1969430859417, 120.737725183126 31.1957176957608, 120.750631138388 31.2094758498508, 120.755164077822 31.2183942385116, 120.761691794471 31.2260786788282, 120.766383015336 31.2262390219568, 120.77505220249 31.2245398494598, 120.786864021641 31.2165282331144, 120.78073703908 31.2074488283747, 120.781312673251 31.1993085656922, 120.798938595154 31.1903083502767, 120.814529855076 31.1839521151415, 120.823815279811 31.20062255802, 120.840090958702 31.2048702812302, 120.8496476717 31.2103041888294, 120.862189633569 31.2168796802276, 120.874179766364 31.2189849381664, 120.885676370111 31.2154355113746, 120.887892579635 31.2188722955582, 120.890998470935 31.2216989741234, 120.900351088922 31.2203508444626, 120.904347470475 31.2217723942111, 120.907013102245 31.2233251653948, 120.905889950866 31.2296351128926, 120.908219077392 31.2309982391137)))'
, '0301030407');