Script para gerar mapa de crimes a partir da planilha de ocorrências do portal de transparência

sp-planilha-crimes-para-geo-wkt.mjs

/*
## Baixar planilha do SSP-SP
wget https://www.ssp.sEp.gov.br/assets/estatistica/transparencia/spDados/SPDadosCriminais_2025.xlsx -O SPDadosCriminais_2025.xlsx

## Converte paginas do XLS para CSV
in2csv -f xlsx --sheet "SPDadosCriminais_2025" SPDadosCriminais_2025.xlsx > SPDadosCriminais_2025.csv

## Filtra apenas S.JOSE DOS CAMPOS records
cat SPDadosCriminais_2025.csv | grep "S. JOSÉ DOS CAMPOS" >> SPDadosCriminais_2025_SJC.csv

## Executa o script
node sp-planilha-crimes-para-geo-wkt.mjs

## Importar os CSVs gerados na camada do https://mymaps.google.com/
*/

import fs from "fs";
import path from "path";

const COLUMNS = {
  NOME_DEPARTAMENTO: 0,
  NOME_SECCIONAL: 1,
  NOME_DELEGACIA: 2,
  NOME_MUNICIPIO: 3,
  NUM_BO: 4,
  ANO_BO: 5,
  DATA_REGISTRO: 6,
  DATA_OCORRENCIA_BO: 7,
  HORA_OCORRENCIA_BO: 8,
  DESC_PERIODO: 9,
  DESCR_TIPOLOCAL: 10,
  DESCR_SUBTIPOLOCAL: 11,
  BAIRRO: 12,
  LOGRADOURO: 13,
  NUMERO_LOGRADOURO: 14,
  LATITUDE: 15,
  LONGITUDE: 16,
  NOME_DELEGACIA_CIRCUNSCRICAO: 17,
  NOME_DEPARTAMENTO_CIRCUNSCRICAO: 18,
  NOME_SECCIONAL_CIRCUNSCRICAO: 19,
  NOME_MUNICIPIO_CIRCUNSCRICAO: 20,
  RUBRICA: 21,
  DESCR_CONDUTA: 22,
  NATUREZA_APURADA: 23,
  MES_ESTATISTICA: 24,
  ANO_ESTATISTICA: 25,
  CMD: 26,
  BTL: 27,
  CIA: 28,
};

const COORDS_PREFEITURA = {
  longitude: -45.87845,
  latitude: -23.18484,
};

const OUT_DIR = "./output";
if (fs.existsSync(OUT_DIR)) {
  fs.rmSync(OUT_DIR, { recursive: true, force: true });
}
fs.mkdirSync(OUT_DIR);

const outputFileMap = {
  "APREENSÃO DE ENTORPECENTES": "DROGAS",
  "PORTE DE ENTORPECENTES": "DROGAS",
  "TRÁFICO DE ENTORPECENTES": "DROGAS",
  "LESÃO CORPORAL CULPOSA - OUTRAS": "LESÃO CORPORAL",
  "LESÃO CORPORAL DOLOSA": "LESÃO CORPORAL",
  "LESÃO CORPORAL SEGUIDA DE MORTE": "LESÃO CORPORAL",
  "FURTO DE VEÍCULO": "FURTO DE VEÍCULO",
  "LESÃO CORPORAL CULPOSA POR ACIDENTE DE TRÂNSITO": "ACIDENTE DE TRÂNSITO",
  "HOMICÍDIO CULPOSO POR ACIDENTE DE TRÂNSITO": "ACIDENTE DE TRÂNSITO",
  "ROUBO DE CARGA": "ROUBO VEÍCULO",
  "ROUBO DE VEÍCULO": "ROUBO VEÍCULO",
  "FURTO - OUTROS": "FURTO",
  "HOMICÍDIO CULPOSO OUTROS": "HOMICÍDIO",
  "TENTATIVA DE HOMICÍDIO": "HOMICÍDIO",
  "HOMICÍDIO DOLOSO": "HOMICÍDIO",
  "ROUBO - OUTROS": "ROUBO",
  LATROCÍNIO: "ROUBO",
  "PORTE DE ARMA": "ARMAS DE FOGO",
  ESTUPRO: "VIOLÊNCIA SEXUAL",
  "ESTUPRO DE VULNERÁVEL": "VIOLÊNCIA SEXUAL",
};

const filesGenerated = {};

const csvFilePath = path.join(process.cwd(), "SPDadosCriminais_2025_SJC.csv");
const content = fs.readFileSync(csvFilePath, "utf8");
const csvRows = parseCSVWithStringDelimiter(content, ",");
for await (const columns of csvRows) {
  if (columns[COLUMNS.NOME_MUNICIPIO] !== "S.JOSE DOS CAMPOS") {
    continue;
  }

  const [
    NOME_DEPARTAMENTO,
    NOME_SECCIONAL,
    NOME_DELEGACIA,
    NOME_MUNICIPIO,
    NUM_BO,
    ANO_BO,
    DATA_REGISTRO,
    DATA_OCORRENCIA_BO,
    HORA_OCORRENCIA_BO,
    DESC_PERIODO,
    DESCR_TIPOLOCAL,
    DESCR_SUBTIPOLOCAL,
    BAIRRO,
    LOGRADOURO,
    NUMERO_LOGRADOURO,
    LATITUDE,
    LONGITUDE,
    NOME_DELEGACIA_CIRCUNSCRICAO,
    NOME_DEPARTAMENTO_CIRCUNSCRICAO,
    NOME_SECCIONAL_CIRCUNSCRICAO,
    NOME_MUNICIPIO_CIRCUNSCRICAO,
    RUBRICA,
    DESCR_CONDUTA,
    NATUREZA_APURADA,
  ] = parseColumns(columns);

  const latitudeRaw = LATITUDE;
  const longitudeRaw = LONGITUDE;

  let latitude = parseFloat(latitudeRaw.replace(",", ".").trim());
  let longitude = parseFloat(longitudeRaw.replace(",", ".").trim());

  const isNaoDivulgado = LOGRADOURO.includes("VEDAÇÃO");
  let isFromOpenStreet = false;
  if (isNaoDivulgado) {
    // Usa coords da prefeitura
    latitude = COORDS_PREFEITURA.latitude;
    longitude = COORDS_PREFEITURA.longitude;
  } else {
    // Tenta encontrar as coors a partir de API se não tiver no CSV
    if (isNaN(latitude) || isNaN(longitude) || !latitude || !longitude) {
      const querys = [
        `${LOGRADOURO.trim()} ${NUMERO_LOGRADOURO} ${BAIRRO} São José dos Campos`,
        `${BAIRRO} São José dos Campos`,
      ].map((q) => q.trim().toUpperCase().replaceAll(/ /g, "+"));

      const results = await Promise.all(
        querys.map((q) => getLatLong(q).catch(() => [])),
      );
      if (!results) {
        throw new Error(
          `Não encontrou coords para: ${LOGRADOURO}, ${NUMERO_LOGRADOURO}, ${BAIRRO}`,
        );
      }

      const coords = await results.find((r) => r && r.latitude && r.longitude);
      latitude = coords.latitude;
      longitude = coords.longitude;

      isFromOpenStreet = true;
    }
  }

  const kmsLat = Math.abs(latitude - COORDS_PREFEITURA.latitude) * 111;
  const kmsLong = Math.abs(longitude - COORDS_PREFEITURA.longitude) * 111;
  const kmsDistance = Math.sqrt(kmsLat * kmsLat + kmsLong * kmsLong);
  const kmsText = `${kmsDistance.toFixed(1)}km ${isFromOpenStreet ? "(via OSM)" : ""}`;

  const [mes, dia, ano] = DATA_OCORRENCIA_BO.split("/");
  const dataBO = new Date(ano, mes, dia).toISOString();

  const dataText =
    new Date(dataBO).toLocaleDateString("pt-BR") +
    (DESC_PERIODO && DESC_PERIODO != "NULL" ? ` (${DESC_PERIODO})` : "");

  const logradouroText =
    `${LOGRADOURO}, N ${NUMERO_LOGRADOURO}, ${BAIRRO} - ${DESCR_TIPOLOCAL} ${DESCR_SUBTIPOLOCAL}`.replaceAll(
      "NULL",
      "",
    );

  const WKT = `POINT (${longitude.toFixed(6)} ${latitude.toFixed(6)})`;
  const name = `${dataText}, ${LOGRADOURO}, N ${NUMERO_LOGRADOURO} - ${DESCR_TIPOLOCAL}`;
  const description = Object.entries({
    ["Natureza Apurada"]: NATUREZA_APURADA,
    ["Rúbrica"]: RUBRICA,
    ["Desc. Conduta"]: DESCR_CONDUTA,
    ["Dt. Ocorrência"]: dataText,
    ["B.O"]: `${NUM_BO}/${ANO_BO}`,
    ["Logradouro"]: logradouroText,
    ["KMs da Prefeitura"]: kmsText,
  })
    .reduce((acc, [key, value]) => {
      if (value !== "NULL") {
        acc.push(`${key}:\t${value}`);
      }
      return acc;
    }, [])
    .join("\n");

  const naturezaApuradaParsed = NATUREZA_APURADA.replaceAll(
    /[/\\?%*:|"<>]/g,
    "-",
  );
  const fileName = outputFileMap[naturezaApuradaParsed] || "_NÃO CATEGORIZADO_";
  const outPath = `${OUT_DIR}/${fileName}.csv`;
  if (!filesGenerated[outPath]) {
    filesGenerated[outPath] = [];
  }
  filesGenerated[outPath].push(`"${WKT}","${name}","${description}"`);
}

Object.keys(filesGenerated).forEach((filePath) => {
  const head = `"WKT","name","description"\n`;
  const content = filesGenerated[filePath].join("\n");

  // Escreve o arquivo como Latin1 (ISO-8859-1) para compatibilidade com o MyMaps
  fs.writeFileSync(filePath, head + content, "latin1");
});

function parseColumns(columns) {
  // 2025
  if (columns.length === 29) {
    return columns;
  }

  // 2024
  if (columns.length === 25) {
    const [
      NOME_DEPARTAMENTO,
      NOME_SECCIONAL,
      NOME_DELEGACIA,
      NOME_MUNICIPIO,
      NUM_BO,
      ANO_BO,
      DATA_REGISTRO,
      DATA_OCORRENCIA_BO,
      HORA_OCORRENCIA_BO,
      DESC_PERIODO,
      DESCR_TIPOLOCAL,
      BAIRRO,
      LOGRADOURO,
      NUMERO_LOGRADOURO,
      LATITUDE,
      LONGITUDE,
      NOME_DELEGACIA_CIRCUNSCRICAO,
      NOME_DEPARTAMENTO_CIRCUNSCRICAO,
      NOME_SECCIONAL_CIRCUNSCRICAO,
      NOME_MUNICIPIO_CIRCUNSCRICAO,
      RUBRICA,
      DESCR_CONDUTA,
      NATUREZA_APURADA,
    ] = columns;

    const DESCR_SUBTIPOLOCAL = "";

    return [
      NOME_DEPARTAMENTO,
      NOME_SECCIONAL,
      NOME_DELEGACIA,
      NOME_MUNICIPIO,
      NUM_BO,
      ANO_BO,
      DATA_REGISTRO,
      DATA_OCORRENCIA_BO,
      HORA_OCORRENCIA_BO,
      DESC_PERIODO,
      DESCR_TIPOLOCAL,
      DESCR_SUBTIPOLOCAL,
      BAIRRO,
      LOGRADOURO,
      NUMERO_LOGRADOURO,
      LATITUDE,
      LONGITUDE,
      NOME_DELEGACIA_CIRCUNSCRICAO,
      NOME_DEPARTAMENTO_CIRCUNSCRICAO,
      NOME_SECCIONAL_CIRCUNSCRICAO,
      NOME_MUNICIPIO_CIRCUNSCRICAO,
      RUBRICA,
      DESCR_CONDUTA,
      NATUREZA_APURADA,
    ];
  }

  return [];
}

function parseCSVWithStringDelimiter(csvString, delimiter = ",") {
  const result = [];

  const lines = csvString.split("\n");
  for (const line of lines) {
    const values = [];
    let currentValue = "";
    let inQuotes = false;

    for (const char of line) {
      if (char === '"') {
        inQuotes = !inQuotes;
      } else if (char === delimiter && !inQuotes) {
        values.push(currentValue.trim());
        currentValue = "";
      } else {
        currentValue += char;
      }
    }

    values.push(currentValue.trim());
    result.push(values);
  }

  return result;
}

function getLatLong(query) {
  const url = `https://nominatim.openstreetmap.org/search?format=json&q=${query}`;
  return fetch(url)
    .then((response) => response.json())
    .then((data) => {
      if (data.length === 0) {
        throw new Error("Nenhum resultado encontrado");
      }

      return {
        latitude: parseFloat(data[0].lat),
        longitude: parseFloat(data[0].lon),
      };
    });
}