Scribd Logo
Upload

Welcome to Scribd!

  • 38 views

    My SQL PDF

    Uploaded by

    Dymas Reynaldi
    This document provides instructions for converting shapefile data to geojson and CSV formats using ogr2ogr commands, loading data into a MySQL database, exporting data from SQL to CSV, and performing operations like inserting values, adding fields, and updating values in SQL tables. It also includes notes on setting configuration options like max_allowed_packet to handle large data imports and running SQL scripts from the command line.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    My SQL PDF

    Uploaded by

    Dymas Reynaldi
    38 views2 pages
    This document provides instructions for converting shapefile data to geojson and CSV formats using ogr2ogr commands, loading data into a MySQL database, exporting data from SQL to CSV, and performing operations like inserting values, adding fields, and updating values in SQL tables. It also includes notes on setting configuration options like max_allowed_packet to handle large data imports and running SQL scripts from the command line.

    Original Title

    My SQL.pdf

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for converting shapefile data to geojson and CSV formats using ogr2ogr commands, loading data into a MySQL database, exporting data from SQL to CSV, and performing operations like inserting values, adding fields, and updating values in SQL tables. It also includes notes on setting configuration options like max_allowed_packet to handle large data imports and running SQL scripts from the command line.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    38 views2 pages

    My SQL PDF

    Uploaded by

    Dymas Reynaldi
    This document provides instructions for converting shapefile data to geojson and CSV formats using ogr2ogr commands, loading data into a MySQL database, exporting data from SQL to CSV, and performing operations like inserting values, adding fields, and updating values in SQL tables. It also includes notes on setting configuration options like max_allowed_packet to handle large data imports and running SQL scripts from the command line.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 2

    My SQL

    Tuesday, August 9, 2022 11:53 AM

    Convert shapefile to geojson

    Ogr2ogr -f GeoJSON targetfile.shp result.geojson

    Upload shapefile to mysql


    ogr2ogr -f MySQL MySQL:indonesia,host=192.168.18.30,user=dymas,password=dymas "C:
    \Users\JamesGTX\Desktop\Dymas\Uji\SHP\To Geojson\SHP\indo_provinsi.shp" -nln indonesia -
    update -overwrite -lco engine=MYISAM

    ogr2ogr -f "CSV" -overwrite MyLayer2.csv MyLayer.shp


    ogr2ogr -f CSV -dialect sqlite -sql "select AsGeoJSON(geometry) AS geom, * from input"
    output.csv input.shp

    ogr2ogr clean_polygons.shp dirty_polygons.shp -dialect sqlite -sql "SELECT


    ST_Buffer(geometry,0), * FROM dirty_polygons"
    ogr2ogr -f CSV -dialect sqlite -sql "select AsGeoJSON(geometry) AS geom, * from provinsi" "D:
    \DATA\CSV\test.csv" "D:\DATA\SHP\Demografi Regional/provinsi.shp"

    Export File From SQL:


    select * into outfile 'C:/Users/dymas/kaggle_explorations/CSV/penduduk.csv' FIELDS
    TERMINATED BY ',' LINES TERMINATED BY '\n' from indo_penduduk;
    INSERT VALUE TO ANOTHER TABLE

    use indonesia_db;
    INSERT INTO indonesia (SHAPE, NAMA1, NAMA2, ID)
    SELECT SHAPE, kecamatan , kec_n, kec_nom
    FROM kecamatan;

    Add New Prefix


    UPDATE bank_all SET NAMA2 = CONCAT('D ', NAMA2) WHERE NAMA2 LIKE '%
    KELURAHAN%'

    Set max allowed packet in SQL:


    set global net_buffer_length=1000000;
    set global max_allowed_packet=1000000000;

    Pada SQL cloud:


    • Set max_allowed_packet pada flags
    • import data via cloud storage

    Lakukan ini sebelum set max allowed:


    GRANT SUPER ON *.* TO 'rosyidi_15'@'localhost' IDENTIFIED BY 'rosyidi';

    run SQL script:


    -u user -p database_name < file_name.sql
    Run batch query mysql

    ALTER TABLE `provinsi` ADD `geojson` LONGTEXT NOT NULL AFTER `prov_nom`;
    UPDATE provinsi

    SET `geojson` = ST_AsGeojson(`SHAPE`)


    ALTER TABLE `provinsi` DROP `SHAPE`;

    PROGRAMING Page 1
    ALTER TABLE `provinsi` DROP `SHAPE`;

    PROGRAMING Page 2

    You might also like